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)

Thanks,

Colin Martin

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
s. Or try turning the query into a UNION.

Baron

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
> s. 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 !

Colin Martin

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
>> s. 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

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

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