Without additional information, SQLite guesses that the data_idx index will
narrow down the search to about 7 entries in the table. This is, of
course, a guess, but it is a reasonable guess for most indices. The
primary key, even though it is unique, has an IN clause with 50 entries, it
SQLite guesses it will narrow the search down to 50 entries. SQLite picks
the index that leads to the least amount of searching: 7 entries versus 50.
In your case, I'm guessing that data_idx is really not a very good index
and might ought to be dropped for doing little more than taking up space.
What does the sqlite_stat1 entry for data_idx say?
I simply narrowed down an example from my application. The data_idx actually
represents an index with 3 columns for a very-commonly used query in the
application which wasn't intended to be used for the query provided.
It sounds like the solution is to just run ANALYZE, then both queries
choose the right index. I was just shocked to find the primary key
not used when referencing rows by primary key.
Thanks.
-Brad
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
