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" <[EMAIL PROTECTED]> wrote on 03/13/2006 10:48:29 AM:
> Shawn,
>
> Any performance gains for specifying "type > 0" than "type <> 0" ?
>
> R.
>
> ________________________________
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 13, 2006 6:37 AM
> To: Robert DiFalco
> Cc: [email protected]
> Subject: Re: Query Optimization Question
>
>
>
>
> "Robert DiFalco" <[EMAIL PROTECTED]> 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
>
