There's a guideline for what sort of things SQLite will look for in an index and a query. There are plenty of more advanced ways to make use of indexes I'm sure, but currently those would fall into "future optimization opportunities." http://www.sqlite.org/optoverview.html
In your case the index on a, b is indeed a candidate for use in the query, but apparently the planner doesn't think it will be more beneficial than the full scan. This is where ANALYZE can help by letting the planner have more info about the indexes. You can always use "INDEXED BY" to force it to use a specific index, and if you get an error along the lines of "Error: no query solution" then you'll know it definitely can't be used, either because of a typo while making it or some other reason. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens Sent: Wednesday, August 09, 2017 10:51 AM To: SQLite mailing list Subject: [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 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
