SQLite Forum

INDEX usage on inequality and IN
You have 1,000,000 rows, and only 4 possible values.
You are searching for all rows other than those with 1 of those 4 values.
The column is indexed, so an index is available.

Under those circumstances the index may be useful.  SQLite would figure that out automatically, and you don't need to write any special SQL to make it happen.  If SQLite doesn't take advantage of the index, it's because it has figured out that reading the whole table and skipping the rows where <code>type=1</code> is faster than using the index.  (Please note that this is more complicated than you think, since SQLite has to retrieve information from the table for the rows you selected, so it has to read the table anyway.)

If you want to be sure SQLite has the best information to choose the fastest methods, put realistic data in the table and use the ANALYZE command:


You can use the command just once.  The results are stored in the database and, unless the character of your data changes radically, you don't need to ANALYZE again.