MySQL uses only one index for a select, so it can't use an index when there is an OR in the where clause.
Ask Bjoern Hansen wrote: > Hi, > > I have a table with about 1.5M rows. > > 9 of the colums are varchar(9)'s. > > when I just select on one of them it goes fine, like: > > >> explain select * from t1 where f2 = 'COM051000'; > > +-------+------+---------------+--------+---------+-------+------+------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra | > +-------+------+---------------+--------+---------+-------+------+------------+ > | t1 | ref | f2_idx | f2_idx | 10 | const | 422 | where used | > +-------+------+---------------+--------+---------+-------+------+------------+ > 1 row in set (0.02 sec) > > (likewise for f1 = ...) > > > But if I use f1 = ... or f2 = ... it doesn't use the index at all. > > >> explain select * from t1 where f2 = 'COM051000' or f1 = 'COM051000'; > > +-------+------+---------------+------+---------+------+---------+------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra | > +-------+------+---------------+------+---------+------+---------+------------+ > | t1 | ALL | f1_idx,f2_idx | NULL | NULL | NULL | 1194779 | where used | > +-------+------+---------------+------+---------+------+---------+------------+ > 1 row in set (0.01 sec) > > I tried running myisamchk -a on the table and now it shows the > cardinality for each key correctly in "show keys from t1", but it > didn't help on the queries. :-) > > I am sure this is something really obvious, but I've no clue (as you > probably can gather from the above). What am I missing? What kind of > thing can I do to make the above query go faster? Any hints would be > appreciated. > > > - ask -- Gerald L. Clark [EMAIL PROTECTED] --------------------------------------------------------------------- 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
