SQLite Forum

INDEX usage on inequality and IN
Imagine you have a table with 1000000 rows. The id column is guaranteed to be unique. So when you search for a specific id value, SQLite knows that it can perform a search in logarithmic time by using the index to find the one true record.

When you search for all rows that don't match a single id value, SQLite knows that you will match either all rows or all but one row. Thus the advantage to using the index does not exist when your query will return N or N-1 rows.

The same holds true for the type column without the uniqueness guarantee. The query plan might be different based on statistics, but at this point in time that is the best information that SQLite has available.