Sqlite is just really smart :) Doing a `count(*)` on my table with one constraint of a two-valued index does a table scan and completes in 9ms, and when I force use of the index, it's 100ms. I'll stop trying to second-guess the query optimizer now ;)
On Wed, Aug 9, 2017 at 5:11 PM Wout Mertens <wout.mert...@gmail.com> wrote: > 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
