List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 13 2006 2:37pm
Subject:Re: Query Optimization Question
View as plain text  
"Robert DiFalco" <rdifalco@stripped> wrote on 03/11/2006 12:43:43 PM:

> In a previous database engine I was using an IN was more optimal than a
> <>. So, for example:
>     SELECT * FROM table WHERE table.type IN (1,2,3);
> Where the possible values of type are 0-3, was appreciably faster than:
>     SELECT * FROM table WHERE table.type <> 0;
> I've been playing with the Query Browser and checking out the
> optimization documents and haven't been able to make a clear call on
> whether or not this is also the case with MySQL/InnoDB.
> TIA,
> R.

YES, YES, YES! This is definitely an optimization. 

When you say IN or =, you are asking for "matching values". Matches can 
come from indexes. When you say <> or NOT IN, you are asking for 
everything BUT matches.  In order to evaluate a negative, the database 
engine (and this is usually true regardless of database server) almost 
always performs a full table scan to test every row to make sure it is 
either <> or NOT IN. At the very best, they have to perform a full index 
scan which is still less efficient than  ranged or values-based lookups.

It's when you get into the situation where you are matching against dozens 
of IN-clause items that you may run into slowdowns again. Until you reach 
2 or 3 dozen terms (depending on your hardware) you should be faster with 
an IN comparison than a <> or a NOT IN comparison. An optimization to 
search for BUT a term or two is to create a temporary table of all of your 
terms and delete the exact ones you want to exclude. Put an index on yoru 
temp table then JOIN that back into your query again (replacing the huge 
IN clause).  The database will match index to index and things will get 
fast again. This technique can scale up to some really big queries.

Always try to code for the affirmative tests. Your users will thank you.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Query Optimization QuestionRobert DiFalco11 Mar
  • Re: Query Optimization QuestionMladen Adamovic11 Mar
  • Re: Query Optimization QuestionSGreen13 Mar
RE: Query Optimization QuestionRobert DiFalco13 Mar
  • RE: Query Optimization QuestionSGreen13 Mar
RE: Query Optimization QuestionRobert DiFalco13 Mar
Re: Query Optimization QuestionMichael Stassen14 Mar
Re: Query Optimization QuestionMichael Stassen14 Mar