Hi,
Joerg Bruehe wrote:
> 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.
Whoops, I overlooked the obvious ;-) Thanks for catching this.
Baron
