Hi all,
Baron Schwartz wrote:
> Hi,
>
> Colin Martin wrote:
>> Baron Schwartz wrote:
>>> Hi,
>>>
>>> Colin Martin wrote:
>>>> Hi there,
>>>>
>>>> Can someone please explain why when the query below uses one
>>>> constant in the WHERE clause, MySQL decides to use the index on the
>>>> 'source' column, and why in the second query where there are two
>>>> constants, it decides not to?
Optimizing "or" is pretty complicated, because (in general) the
alternatives may access different columns etc.
I know your case is a simple one, but still ...
>>>>
>>>> Is there a way to get MySQL to use the index for the second query?
>>>>
>>>> mysql> explain select * from data_total where source=8;
>>>>
>>>> | id | select_type | table | type | possible_keys | key |
>>>> | 1 | SIMPLE | data_total | ref | source | source |
>>>>
>>>>
>>>> mysql> explain select * from data_total where (source=8 or source=9);
>>>>
>>>> | id | select_type | table | type | possible_keys | key |
>>>> | 1 | SIMPLE | data_total | ALL | source | NULL |
>>>>
>>
>> [[...]]
>>
>> Unfortunately, an IN() clause gives the same result. I've even tried
>> FORCE INDEX on it, but it doesn't seem to find a candidate key.
>>
>> If we're stuck with a UNION, then it'll take some re-writing of
>> reports, especially as there may be as many as 5 or 6 different
>> 'sources' required. Unfortunately going through a database upgrade
>> isn't an option at the moment.
>
> I think the UNION is your only option.
Have you tried BETWEEN ?
As long as your "source" values are continuous, it would be applicable,
and "source BETWEEN 8 and 9" is still a single condition.
I do not claim it *will* be optimized better, but it *might*.
If you also need to query for "source" value lists with gaps, things
become different.
HTH,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
