Skip to content
Advertisement

Why is the != operator faster than the = operator?

I have a SQLite database and I perform the following query:

This query takes around 0.3 seconds and returns 8000 rows. Here is the associated query plan:

Now, I would like to select only one category. So, I rewrote my WHERE clause like this:

This query takes now around 43 seconds and returns 5000 rows! It is 100 times longer! Here is the associated query plan:

Why is the equals operator so slow? Is there anything wrong in my query?


Reproduction steps:

  1. Create the following tables
  1. Use the following Perl script to populate the tables

Advertisement

Answer

The difference in the first step is understandable : = allow SQLite to look up the rows for the given value (SEARCH) while != requires it to go through all the values (SCAN)

The question is : Why does it create a temporary (AUTOMATIC) index (which happen to be very effective) in the first case, not in the second ?

I think it’s because, without additional informations, the query planner assumes that an = clause will yield fewer matches than a !=, and the cost of building a temporary index is not justified.

The solution is to let SQLite gather information with ANALYZE. The query plan becomes :

Another solution is create the required index, so that SQLite doesn’t have to build it.

With the following query plan (even without analyse) :

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement