I live my life one "indexed by" at a time. - Deon
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 2, 2017 7:10 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Non-unique columns in unique index On 2017/03/02 4:44 PM, Keith Medcalf wrote: > On Thursday, 2 March, 2017 06:04, Hick Gunter <h...@scigames.at> wrote: I think what Hick tried to show was that if you have a covering Index on fields F1 and F2 (Unique or not) and then have another index (Automatic or not, but Unique) on just F1, and you then do a Query of the form: SELECT F1,F2 FROM T WHERE F1 = x AND F2 = y SQLite will use the covering Index (as expected), but if you drop one WHERE term so as to end up with the form: SELECT F1,F2 FROM T WHERE F1 = x Then SQLite will use the other Index when the covering Index is really better (for read-speed) because it contains all the fields referenced and we "know" that F1 is Unique so the Covering Index must still be Unique for F1. So an Optimization would be that if we "know" F1 to be Unique, and all the fields required (in the SELECT) are found in the covering Index, then using the covering Index will be better. While I follow the suggestion, I'd like to point out that a covering Index might not be the best to use. More than one column could be Unique or the covering Index may contain a lot more fields than is referenced, it may not always be faster. Imagine these tables: CREATE TABLE T (ID INTEGER PRIMARY KEY, F1, F2, F3, F4, F5, F6, F7, F8, F9); CREATE UNIQUE INDEX TU_1 ON T(F1, F2); CREATE INDEX TC_2 ON T(F1, F2, F3, F4, F5, F6, F7, F8, F9); The query: SELECT F1, F2 FROM T WHERE F1 = x AND F2 = y; will surely be much faster when using the implied Unique index, and further: SELECT F3 FROM T WHERE F1 = x AND F2 = y; should still be faster using the (much smaller) Unique Index and reading F3 after a lookup. If you are in doubt, imagine the same example with Fields going up to F999. At some field-count the Unique Index will be faster for any selection of field types. It's hard to imagine a safe tweak for the QP here, and having to "assess" whether all referenced fields are ALSO in Unique indices... sounds like an expensive step, but now I'm just guessing. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
