>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.
Sir, your SQL statements are generating cartesian products, which
force MySQL to examine all rows of one or both tables. This makes
your indices useless, so MySQL doesn't use them.
And now for some nitpicking:
A KEY is not a key. A KEY in MySQL is a non-unique index that allows
nulls. A key contains unique values and doesn't contain nulls.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
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
