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
