List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:October 4 2001 1:41pm
Subject:Re: optimization problem in where clause (f1="Foo" or f2="Foo")
View as plain text  
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
gerald_clark@stripped

Thread
optimization problem in where clause (f1="Foo" or f2="Foo")Ask Bjoern Hansen4 Oct
  • Re: optimization problem in where clause (f1="Foo" or f2="Foo")Gerald Clark4 Oct