On 4/14/16, Richard Hipp <drh at sqlite.org> wrote: > On 4/14/16, John Found <johnfound at asm32.info> wrote: >> But after some time working in wild (executing the more complex original >> query), the performance decreased again and despite of existing the >> index, >> the result of the "explain query plan" is again: >> >> SCAN TABLE T >> USE TEMP B-TREE FOR ORDER BY > > Can you make a copy of the database when it gets into this state, and > send it to us for analysis?
[The database was sent in off-list. This post is a follow-up.] It appears that you did the CREATE INDEX after running ANALYZE because there is no entry in the sqlite_stat1 table for the index you think should be used and the absence of an sqlite_stat1 entry for that index while entries are available for other indexes on the same table has the query planner confused. As it happens, we checked in a change for this very same problem about 10 days ago. See http://www.sqlite.org/cgi/src/info/e375fe52cea7903c for the patch. Please try using the latest trunk version of SQLite which should solve your problem. Or (better) always rerun ANALYZE after doing a CREATE INDEX. -- D. Richard Hipp drh at sqlite.org
