>How-To-Repeat:
drop table if exists t1;
create table t1 (
e enum('N', 'Y') not null,
i int not null,
c1 char(255), c2 char(255), c3 char(255), c4 char(255),
c5 char(255), c6 char(255), c7 char(255), c8 char(255),
primary key (e, i)
);
insert into t1 (e, i) values
('N', 1), ('Y', 2), ('N', 3), ('Y', 3), ('Y', 4), ('N', 6),
('Y', 6), ('N', 7), ('Y', 7), ('Y', 8), ('N', 9), ('Y', 9);
explain select * from t1 where i in (2, 5, 7);
explain select * from t1 where i in (2, 5, 7) and e in ('', 'N', 'Y');
>Release: mysql-4.0.3-beta (Source distribution)
>Description:
Both selects (without explain) return exactly the same because e is
enum('N', 'Y'), but the first one uses the index, the second doesn't:
explain select * from t1 where i in (2, 5, 7);
table type possible_keys key key_len ref rows Extra
t1 ALL NULL NULL NULL NULL 12 where used
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
It is well documented in the chapter "MySQL Optimisation" that the
first select doesn't use the index:
"If you are _always_ using many columns, you should use the column
with more duplicates first to get better compression of the index."
"Any index that doesn't span all AND levels in the WHERE clause is not
used to optimise the query."
But I think it is a good idea to make MySQL more intelligent when
choosing an index so that it uses the index in the first select as
well.
Regards
Julian
---------------------------------------------------------------------
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
