Interesting, that seems like an optimization the query optimizer could do itself when it sees a <> operator on a indexed numeric.
________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 8:01 AM To: Robert DiFalco Cc: mysql@lists.mysql.com 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" <[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: mysql@lists.mysql.com > 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 >
