Great explanation, thanks -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Wednesday, December 20, 2017 10:41 AM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> 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 http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
