Thank you, that is exactyy what I intended. If sqlite would choose to use the covering index in cases where read speed (plus row decode) is likely to be better, then it would already "sort of" support the INCLUDE syntax requested by the OP with only minor changes.
Note that my investigation involved sqlite version 1.7.14.1; maybe the NGQP already handles this efficiently. Also thanks to Keith for pointing out the AUTOMATIC label for ad-hoc generated indices. BTW: My first name is Gunter >-----Ursprüngliche Nachricht----- >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von R Smith >Gesendet: Donnerstag, 02. März 2017 16:10 >An: sqlite-users@mailinglists.sqlite.org >Betreff: 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. > ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
