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?
>>>
>>> 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 |
>>>
>>> (Output chopped for better formatting)
>>
>> I'd need more info to know for sure, but I'm guessing you're using
>> pre-MySQL 5 or MySQL is deciding it's cheaper to scan the table. If
>> it's too slow, try using an IN(8, 9) clause instead and see if that
>> helps. Or try turning the query into a UNION.
>>
>> Baron
>
> You're right that it is pre version 5. It's MySQL 4.1. Any particular
> reason this would make a difference in such a simple case?
>
> The table is about a million rows so I doubt MySQL would decide it's
> cheaper to scan the whole table, or at least if it is I can't figure out
> why!
>
> 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.
Baron
