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

Reply via email to