Re: reate index implies analyze?

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Re: reate index implies analyze?

Nelson, Erik - 2
Great explanation, thanks

-----Original Message-----
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.

This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at   If you are not the intended recipient, please delete this message.
sqlite-users mailing list
[hidden email]