On Thu, 14 Apr 2016 13:57:20 -0400 Richard Hipp <drh at sqlite.org> wrote:
> 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. Thanks for the help. I am not sure, I can compile the code from the repository (because of my low C/C++ programming skills), but will try with the latest amalgamation from the download page. Hope it will be enough. > Or (better) always rerun ANALYZE > after doing a CREATE INDEX. > Optimizing further on the same database (but the table Posts), I hit the reverse problem. The index: create index idxPostsThreadUser on posts(threadid, userid); works only if I do not run "ANALYZE" after dropping and recreating it. :) Hope, the update will fix this problem as well. Once again, thanks for the help. -- http://fresh.flatassembler.net http://asm32.info John Found <johnfound at asm32.info>
