Your experiments are not reproducible unless you provide at least an indication 
of the schema.

Most probably, something you have not yet considered/revealed makes using the 
index to look up a irrelevant. Maybe a is declared as "integer primary key", 
making it an alias of the rowid.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Wout Mertens
Gesendet: Mittwoch, 09. August 2017 16:51
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [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


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to