Hick Gunter Sent: Wednesday, December 20, 2017 10:51 AM >You are not showing the definition of data. Some table constraints (e.g. >UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous >index that duplicates an automatically created index only serves to waste >space in the file and time to maintain.
The table definition doesn't have any constraints/primary keys, simply (note that 'id' is not unique) Create table path_data(id int, path int, month int) >It is possible that creating the unique index first may speed up creation of >the second index containing subset of the fields similar to when a covering >index is used for a query. You will just have to try this out for yourself. >You may find the .timer command of the SQLite shell useful. Be careful to >exclude the effects of caching, as these may swamp any differences in speed. Right, I was hoping for general comments on the index creation order, hoping not to pessimize the application. It's not a big thing either way... the database and temp store is in memory with journal mode off if that makes a difference, and it takes a day or more to populate the table so this would not be a first order performance issue in any event. I was just curious if anyone had insight into the relative merits of varying the index creation order. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Nelson, Erik - 2 Gesendet: Mittwoch, 20. Dezember 2017 16:38 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] performance impact of index creation order Assuming that table 'data' is completely constructed, does index creation order have any performance ramifications? For example, would it be reasonable to assume that the order of these two statements has no performance impact? (all fields are integer in this case) create unique index data_index_1 on path_data(id, path, date) create index data_index on data(path, date) ---------------------------------------------------------------------- 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
