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
