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