On Mon, Jul 8, 2013 at 12:10 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> This question mostly applies to SQLite4, where a number of things will be
> changing anyway.
>
> How do folks feel about the ANALYZE command ? I started off thinking
> about wanting an 'ANALYZE OFF' command to delete the tables that ANALYZE
> makes up. But that just made me think that the ANALYZE command itself
> should really be a PRAGMA, so you could use the normal syntax of
>
> PRAGMA analyze = OFF
>
> or even query the status to see whether there were ANALYZE tables or not.
> What does the team think ?
>
SQLite has an ANALYZE command because PostgreSQL has (or at least had) an
ANALYZE command and I copied the SQLite syntax directly from the PostgreSQL
documentation.
To turn it off, run:
DELETE FROM sqlite_stat1;
DELETE FROM sqlite_stat3;
ANALYZE sqlite_master;
The last command is not necessary if you close and reopen the database.
If you have a schema that contains lower-quality indices, you really need
to run with ANALYZE enabled. Otherwise, SQLite has no way of knowing that
the indices are of low quality and might end up using them when it
shouldn't resulting in slow query performance. A low-quality index (for
the purpose of this paragraph) is one in which the table contains more than
approximately 15 entries with the same value for the right-most column in
the index.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
