Indeed, in trying to reproduce on a simple table it does use the index, even with json_extract values. I must be doing something wrong in my app, thanks.
On Wed, Aug 9, 2017 at 5:07 PM David Raymond <david.raym...@tomtom.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users