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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
