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.

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.

-----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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to