Hello All,
I've bumped into a problem and can't figure out how to solve it.
If anyone has a spare minute to give me a hint on what's wrong
I'd apreciate it a lot!
The problem is that when I add 'use index (<multi-part index>)'
to my queries, MySQL doesn't always use the whole key,
but decides what left-most part of the key to use itself.
Sometimes its decisions are rather strange to me.
Here are some tests that show what I mean:
CREATE TABLE test3 (
pr mediumint(8) unsigned NOT NULL default '0',
a mediumint(8) unsigned default '0',
b mediumint(8) unsigned default '0',
data mediumint(8) unsigned default '0',
KEY a (a),
KEY b (b),
KEY k (a,b)
);
INSERT INTO test3 VALUES (1,0,0,9);
INSERT INTO test3 VALUES (2,0,1,9);
INSERT INTO test3 VALUES (3,1,0,9);
INSERT INTO test3 VALUES (4,1,1,9);
INSERT INTO test3 VALUES (5,1,2,9);
INSERT INTO test3 VALUES (6,0,0,9);
Test 1
======
explain select R.data from test3 as R, test3 as L use index (k) where
L.a=IF(R.a=0,1,0) and
L.b>R.b;
##### The result is OK (just what I expected):
+-------+-------+---------------+------+---------+------+------+------------
-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+-------+---------------+------+---------+------+------+------------
-------------+
| R | ALL | b | NULL | NULL | NULL | 6 |
|
| L | index | a,b,k | k | 8 | NULL | 6 | where used;
Using index |
+-------+-------+---------------+------+---------+------+------+------------
-------------+
Test 2
======
explain select R.data from test3 as R, test3 as L use index (k) where
((L.a=0 and R.a=1) or (L.a=1 and R.a=0)) and
L.b>R.b;
##### Why doesn't MySQL use the whole k index (8 bytes)?
+-------+-------+---------------+------+---------+------+------+------------
-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+-------+---------------+------+---------+------+------+------------
-------------+
| R | ALL | a,b,k | NULL | NULL | NULL | 6 | where used
|
| L | range | a,b,k | k | 4 | NULL | 5 | where used;
Using index |
+-------+-------+---------------+------+---------+------+------+------------
-------------+
Test 3
======
explain select R.data from test3 as R, test3 as L use index (k) where
L.a>R.a and
L.b>R.b;
##### k key isn't choosen by MySQL. Why?
+-------+------+---------------+------+---------+------+------+-------------
---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+------+-------------
---------------------------------+
| R | ALL | a,b,k | NULL | NULL | NULL | 6 |
|
| L | ALL | k | NULL | NULL | NULL | 6 | range
checked for each record (index map: 4) |
+-------+------+---------------+------+---------+------+------+-------------
---------------------------------+
Test 4
======
explain select R.data from test3 as R, test3 as L use index (k) where
R.a=0 and
L.a=0 and
L.b>R.b;
##### Why doesn't MySQL use the whole k index (8 bytes)?
+-------+------+---------------+------+---------+-------+------+------------
-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+-------+------+------------
-------------+
| R | ref | a,b,k | a | 4 | const | 2 | where used
|
| L | ref | a,b,k | k | 4 | const | 2 | where used;
Using index |
+-------+------+---------------+------+---------+-------+------+------------
-------------+
The bottom line is that I'd like to know if MySQL has a feature that
makes it use a key even if the optimizer doesn't think it'a good one.
Thanks in advance.
Regards,
Alexander Paperno.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
