Thanks for input. So you're saying that creating the tables manually is not the same as issuing the "ANALYZE" command? However, ".dump sqlite_stat2" does NOT work. And I have been using the SQLITE_ENABLE_STAT2 switch for decades now :P This is what this command produces: PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT;
------------------------------------------------------------- A bug? > From: d...@sqlite.org > Date: Mon, 7 Feb 2011 14:42:06 -0500 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans" > > On Mon, Feb 7, 2011 at 1:48 PM, Sven L <larvpo...@hotmail.se> wrote: > > > > > Yes, this is what I have read on the net too. But, tests show that without > > the sqlite_stat* tables, my queries are ridiculously slow (probably unwanted > > table scans etc). > > Real-life data... can't I simply take my real-life database and extract the > > data in sqlite_stat*? > > > > You can take a sample database that is representative of what to expect for > most of your users, run ANALYZE, then do ".dump sqlite_stat1". Save the > output. Then when creating a new database for a user, after doing all your > CREATE TABLE and CREATE INDEX statements, but before adding actual content, > run ANALYZE there. The ANALYZE command will take a millisecond or so since > there is no data. > > Then run the "INSERT INTO sqlite_stat1" statements you saved in order to > fill the stat1 table with content from your "sample" database. > > > > > > Btw, this command produces nothing even though the table does contain > > several rows: ".dump sqlite_stat2" > > > > Compile with SQLITE_ENABLE_STAT2 in order to get the stat2 information. > stat2 is a 10-sample histogram of the content of each index. This enables > SQLite to make smarter choices about when to use index lookup and when to do > table scans. For example: > > SELECT * FROM tab WHERE x=0; > > The above might do a table scan if a majority of the tab.x values are 0. > But if only 1% of the tab.x values are 0, then it will use an index. > > > > > > Thanks > > > > > From: slav...@bigfraud.org > > > Date: Mon, 7 Feb 2011 16:44:00 +0000 > > > To: sqlite-users@sqlite.org > > > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans" > > > > > > > > > On 7 Feb 2011, at 4:38pm, Sven L wrote: > > > > > > > So, with this in mind, it makes sense to precompute the sqlite_stat* > > tables. Right? > > > > > > Which you do by running ANALYZE, but since it needs real-life data to > > work on there's no point doing it until your customer has put some data in. > > I don't write this type of application any more, but I might put it in a > > maintenance routine -- some obscure menu option near the Config Preferences > > or something. Run it as part of your yearly maintenance procedure, after > > you've run 'PRAGMA integrity_check'. > > > > > > Simon. > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
