On Mon, Sep 2, 2013 at 9:42 AM, Bert Huijben <rhuij...@apache.org> wrote:
> > My question is more like: Should I see this as a light regression that most > likely will be resolved in a future version, or as a buggy assumption in > our > code that I should work around? It should be possible via explicit 'INDEXED > BY' clauses and/or breaking the query in separate parts. (Or for future > versions by pre-filling the stat table) > I think rules #3 and #4 (especially #4) of http://www.sqlite.org/queryplanner-ng.html#howtofix are applicable here. Without the results of ANALYZE, SQLite assumes that the left-most column of an index will narrow down a search to about 10 rows. That is clearly not the case with your schema (where the left-most column of several indices, wc_id, is always the same value). Hence, SQLite really needs the results of ANALYZE to work efficiently. But that does not mean you have to run ANALYZE on each installation. Instead, you can install a pre-computed sqlite3_stat1 table (the larger of the two you emailed above would suffice) whenever you create a new database. To do this, just run: ANALYZE sqlite_master; -- Create the sqlite_stat1 table INSERT INTO sqlite_stat1 VALUES(...); -- repeat as necessary to fill in the table. ANALYZE sqlite_master; -- Load the sqlite_stat1 table into the optimizer. The above steps only need to be done once, when the database is first created, and can be part of the same script that creates all the other tables, indices, triggers, and views in your database. You should never need to run ANALYZE again (assuming the "shape" of your data is always roughly the same). The sqlite_stat1 table created here gives SQLite all the information it needs to be able to figure out the best way to handle queries in your peculiar usage pattern. FWIW, please note that the I_NODES_MOVED index in your schema is completely useless, since all values of all columns are always the same. That index is dead weight. You might want to drop that index, if the shape of your data really is always similar to the examples you supplied us. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
