MySQL Forums
Forum List  »  Newbie

Re: Efficient string comparison to a group of values
Posted by: Rick James
Date: April 02, 2009 01:04AM

First of all it will NOT use an index on county, but not because of IN. Instead because you are not testing on county, itself, but some function of county.

Furthermore the function is probably unnecessary -- you probably are using a "case insensitive" collation, like latin1_ci or utf8_unicode_ci, that obviates the need for lower(...).)

Now that I have sidetracked twice, back to your question about query optimization of IN:

WHERE x IN (1,2,4)
may or may not be able to use an index on x.

If it can't use the index, and there is nothing else in the WHERE clause (like "AND name='Jones'"), then it will be a table scan. This is slow. (You won't notice the slowness if you have only 1000 rows.)

If it does decide to use an index on x, it might turn it into a "range" scan:
WHERE x BETWEEN 1 AND 4
and then filter out any that don't match (1,2,4).

Or it might actually probe for 1, then 2, then 4.

Use EXPLAIN to discover which does in your case -- with the real SELECT, and a real table, with real data. (As I say, it may vary!)

If you get into millions of records, and if it decides to punt on the index, the I recommend using UNION:
SELECT ... WHERE county = 'Kent'
UNION
SELECT ... WHERE county = 'Surrey'
...
use UNION ALL unless you need UNION DISTINCT.

Options: ReplyQuote


Subject
Written By
Posted
Re: Efficient string comparison to a group of values
April 02, 2009 01:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.