CODEWITHSUNDEEP
sqloptimizationfirebird2.5
Novice
Novice

Reputation: 145

Optimizing basic SELECT query with multiple NOT IN values

Which would be more cost effective way to create a basic SELECT query. Option one:

SELECT id 
FROM table 
WHERE COL0 NOT IN (2,3,4,5,6,7,8,9...) 
  AND COL1 >= 20 
  AND COL2 <= 10 
  AND .... ;

Or option two: 

SELECT id FROM table WHERE COL0 NOT IN (2,3,4,5,6,7,8,9...);

The COL0 is FK column.

The first thing necessary would be index on the COL0. But from there..

  1. The number included in the NOT IN clause could be from 1 to 1000 for example.

Questions:

  1. Would the additional values in the WHERE clause help the DB to perform the query faster by eliminating stuff that should not be in the response, or will it just be additional work to check the accordance to the additional values?
  2. Theoretically having hundreds of ID values in the NOT IN clause would be considered as bad and "expensive" design?

I'm using Firebird 2.5 .

Upvotes: 0

Views: 83

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The db query optimizer will use the best index to filter the most number of rows.

So you should use first aproach and add either:

  • separate index for col0, col1 and col2
  • composite index for both (col0, col1, col2)

so imagine you have 1000 rows but only 10 are > 20 optimizer will use the col1 index to filter out 990 rows making the rest of the query faster.

Also instead of use NOT IN you could save those value in a separated table tblFilter

SELECT id 
FROM table T1 
LEFT JOIN tblFilter T2
       ON T2.col0 = T2.col0
WHERE T2.col0 IS NULL

Upvotes: 2

Related Questions