List:General Discussion« Previous MessageNext Message »
From:Robert DiFalco Date:March 13 2006 4:09pm
Subject:RE: Query Optimization Question
View as plain text  
Interesting, that seems like an optimization the query optimizer could
do itself when it sees a <> operator on a indexed numeric.

________________________________

From: SGreen@stripped [mailto:SGreen@stripped] 
Sent: Monday, March 13, 2006 8:01 AM
To: Robert DiFalco
Cc: mysql@stripped
Subject: RE: Query Optimization Question



Yes, a ranged query should respond faster than a negation. In some cases
you can seriously improve query performance for a negation query if you
split it into two range queries unioned together. Here is a pseudo
example: 

This query should be slow due to the table scan it takes to test the
condition: 

SELECT ... 
FROM ... 
WHERE indexfield <> 16 

This query should be noticeably faster due to using ranged queries
(partial index ranged matches): 

(        SELECT ... 
FROM ... 
WHERE indexfield < 16 
) UNION ( 
        SELECT ... 
FROM ... 
WHERE indexfield > 16 
) 

Of course, if you have to do a table scan ANYWAY (because a value you
have in a constraint is not in an index) then this won't help. This
optimization is all about getting the engine to use an index whenever it
can instead of performing a table scan. Of course, that is not always
possible. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

"Robert DiFalco" <rdifalco@stripped> wrote on 03/13/2006 10:48:29
AM:

> Shawn,
>  
> Any performance gains for specifying "type > 0" than "type <> 0" ?
>  
> R.
> 
> ________________________________
> 
> From: SGreen@stripped [mailto:SGreen@stripped] 
> Sent: Monday, March 13, 2006 6:37 AM
> To: Robert DiFalco
> Cc: mysql@stripped
> Subject: Re: Query Optimization Question
> 
> 
> 
> 
> "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
> 


Thread
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