Your experiments are not reproducible unless you provide at least an indication of the schema.
Most probably, something you have not yet considered/revealed makes using the index to look up a irrelevant. Maybe a is declared as "integer primary key", making it an alias of the rowid. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Wout Mertens Gesendet: Mittwoch, 09. August 2017 16:51 An: SQLite mailing list <[email protected]> Betreff: [sqlite] Using multi-value indexes for subset queries Hi all, in experimenting with indexes I found that if you create an index on (a, b) and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index, great. However, if you write SELECT * FROM data WHERE a = 1, it won't use the index. If you write SELECT * FROM data WHERE a = 1 AND B != some_impossible_value, it does use the index. Is it not efficient to use an index, even if one of its values is not constrained? Case in point, I have an app that allows searching across 4 of parameters, and I am wondering if I could just use a single 4-valued index, searching for != impossible_val for unconstrained parameters. Surely that would be a better use of resources than indexes on all combinations? And if that is indeed the case, would it not be good that sqlite automatically checks multi-value indexes where it can't find a specific index? Wondering Wout. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ 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: [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
