From: sqlite-users [mailto:[hidden email]] On Behalf Of Hick Gunter
Sent: Wednesday, December 20, 2017 10:41 AM
To: 'SQLite mailing list' <[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] create index implies analyze?
CREATE INDEX will populate the index with references to all of the rows in the table. Bulk loads may run considerably faster if no indices are present at load time (not yet created or dropped beforehand), but created right after the data has been inserted. If you run INSERT or UPDATE statements later, they will also update the available indices too.
ANALYZE does NOT "populate" an index. It attempts to deduce the "shape" of your data, so that a faster query plan may be selected for queries that require ordering and/or joining several tables.
Running ANALYZE is only necessary if the "shape" of your data has changed, e.g. you have loaded a large dataset that is different from what you had before. Note that it is also possible to extract the results of ANALYZE from a "production sized" database and insert them into an empty database (with identical schema, of course), so that running ANALYZE is not required at all even after loading production data.