Julian,
Wednesday, September 25, 2002, 2:20:05 PM, you wrote:
JL> drop table if exists t1;
JL> create table t1 (
JL> e enum('N', 'Y') not null,
JL> i int not null,
JL> c1 char(255), c2 char(255), c3 char(255), c4 char(255),
JL> c5 char(255), c6 char(255), c7 char(255), c8 char(255),
JL> primary key (e, i)
JL> );
JL> insert into t1 (e, i) values
JL> ('N', 1), ('Y', 2), ('N', 3), ('Y', 3), ('Y', 4), ('N', 6),
JL> ('Y', 6), ('N', 7), ('Y', 7), ('Y', 8), ('N', 9), ('Y', 9);
JL> explain select * from t1 where i in (2, 5, 7);
JL> explain select * from t1 where i in (2, 5, 7) and e in ('', 'N', 'Y');
JL>Release: mysql-4.0.3-beta (Source distribution)
JL>Description:
JL> Both selects (without explain) return exactly the same because e is
JL> enum('N', 'Y'), but the first one uses the index, the second doesn't:
JL> explain select * from t1 where i in (2, 5, 7);
JL> table type possible_keys key key_len ref rows Extra
JL> t1 ALL NULL NULL NULL NULL 12 where used
JL> explain select * from t1 where i in (2, 5, 7) and e in ('', 'N', 'Y');
JL> table type possible_keys key key_len ref rows Extra
JL> t1 range PRIMARY PRIMARY 5 NULL 9 where used
JL> It is well documented in the chapter "MySQL Optimisation" that the
JL> first select doesn't use the index:
JL> "If you are _always_ using many columns, you should use the column
JL> with more duplicates first to get better compression of the index."
JL> "Any index that doesn't span all AND levels in the WHERE clause is not
JL> used to optimise the query."
JL> But I think it is a good idea to make MySQL more intelligent when
JL> choosing an index so that it uses the index in the first select as
JL> well.
If you change primary key (e, i) to primary key (i, e) MySQL will use index in
both query.
mysql> explain select * from t1 where i in (2, 5, 7);
+-------+-------+---------------+---------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+---------+---------+------+------+------------+
| t1 | range | PRIMARY | PRIMARY | 4 | NULL | 4 | where used |
+-------+-------+---------------+---------+---------+------+------+------------+
1 row in set (0.01 sec)
mysql> explain select * from t1 where i in (2, 5, 7) and e in ('', 'N', 'Y');
+-------+-------+---------------+---------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+---------+---------+------+------+------------+
| t1 | range | PRIMARY | PRIMARY | 5 | NULL | 9 | where used |
+-------+-------+---------------+---------+---------+------+------+------------+
1 row in set (0.00 sec)
Why? Read about how MySQL works with multi-column indexes:
http://www.mysql.com/doc/en/Multiple-column_indexes.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
