SQLite Forum

JOINs with FTS5 virtual tables are very slow
> ANALYZE can take a long time

I just ran ANALYZE on the 93.7MB SQLite Fossil repository on my desktop
and it took 161 milliseconds.

But, yeah, depending on the database and the schema and the hardware on which it is running, ANALYZE can sometimes be expensive as it does have to do a full scan of every index.  Finding ways to make this work better is an 
area of active research.  The new [approximate-analyze][approx] branch,
for example, is intended to be a place where I can experiment with ideas of
computing a reasonable analysis using sampling of each index rather than
reading each index from end to end.  (I had hoped to get to work on that some
today, but I might end up spending the whole day working on this forum...)

See also the [PRAGMA optimize][opt] command, which is intended to be run by
applications as they are shutting down.  The PRAGMA optimize command might
run an ANALYZE on one or two indexes, on an as needed basis, but it does not
typically run a complete ANALYZE on all indexes, and so it tends to be faster.
And it knows how to rerun ANALYZE when the content of the database changes
significantly. Fossil has been [using PRAGMA optimize][fossil-use] for about
3 years now and that has worked out quite well.

[approx]: https://sqlite.org/src/timeline?r=approximate-analyze
[opt]: https://sqlite.org/pragma.html#pragma_optimize
[fossil-use]: https://fossil-scm.org/fossil/artifact/d75f4c685956c943?ln=1975