On Mon, Feb 7, 2011 at 4:37 PM, Sven L <[email protected]> wrote: > > Thanks for input. So you're saying that creating the tables manually is not > the same as issuing the "ANALYZE" command? >
You are not allows to "CREATE TABLE sqlite_stat1" or "DROP TABLE sqlite_stat1". (You cannot CREATE or DROP any table whose name begins with "sqlite_".) But you can insert or delete those tables once created. So you have to run ANALYZE at least once to create the tables before you populate them. > > 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: [email protected] > > Date: Mon, 7 Feb 2011 14:42:06 -0500 > > To: [email protected] > > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans" > > > > On Mon, Feb 7, 2011 at 1:48 PM, Sven L <[email protected]> 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: [email protected] > > > > Date: Mon, 7 Feb 2011 16:44:00 +0000 > > > > To: [email protected] > > > > 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 > > > > [email protected] > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > [email protected] > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > D. Richard Hipp > > [email protected] > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
