On 4 Oct 2017, at 2:23am, Howard Kapustein <howard.kapust...@microsoft.com> 

> 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 

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.

sqlite-users mailing list

Reply via email to