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
