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."

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 [] 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,

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

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

Wondering Wout.
sqlite-users mailing list
sqlite-users mailing list

Reply via email to