On 4 Oct 2017, at 2:23am, Howard Kapustein <howard.kapust...@microsoft.com> wrote:
> What do you think of an option to only analyze tables that have grown from 0 > records to >0 e.g. PRAGMA optimize(0x10002) so 2=ANALYZE if beneficial and > 0x10000=change the criteria from "increased by 25 times or more since the > last" to "increased from 0 records to 1+ record since the last? ANALYZE builds a table of statistics which tells SQLite which search strategy will be optimal. The table of statistics does not have to exactly match the data in your tables (in fact that’s very rare). The big changes noted by ANALYZE are not to do with the number of rows in each table, they are to do with the 'chunkiness' of each indexed column: whether a column has only two values (indoor / outdoor) or thousands of different values (surname). And 'chunkiness' doesn’t change much once a database is big enough that search-time matters. It is not expected that you’ll try to run ANALYZE while a database is in use. It’s intended for offline-maintenance. So why not work around the whole process ? On your development system, create a database with plausible data in. Run ANALYZE. Then copy all the tables named "sqlite_stat*" into a new database. You can then copy those tables into the databases for your production system. They will 'tune' SQLite to pick strategies which work best for a typical dataset. You never have to run ANALYZE or "PRAGMA optimize" on that system. Yes, it won’t give absolutely the fastest possible operations on your production system(s). But they will be very close to it, and you have the advantage of never having to lock the database up with a maintenance procedure. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
