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.
Thanks very much for your help!
Colin Martin
