From: Colin Martin
Date: October 25 2007 10:38am
Subject: Re: Index usage for simple query
List-Archive: http://lists.mysql.com/mysql/209683
Message-Id: <4720723F.7000006@electriciq.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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