An index is only usable for that subset of a queries' equality constraints that 
forms a leading subset of the fields handled by the index.

E.g.if  you are looking at equality constraints for fields a, b and c in one 
query, then you need an index whose first three fields are a, b and c (in any 
order).

You are obviously creating separate indexes on each field, so these indexes 
will only speed up 1 constraint. An index on field a has no idea of the values 
of b for the rows with a given value of a, so SQLite needs to visit each row 
with that value of a. Only an index on (a,b) or (b,a) will allow you to find 
the rows having specified values for a and b without visiting any others.

If you have a specific set of queries that your application uses (i.e. a set of 
questions that arise from the real world process you are modelling), you can 
create indexes on all the combinations of constraints, load a representative 
dataset, run ANALYZE and then retrieve the query plans for all of your queries, 
and then drop all the indices that are never used.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Wout Mertens
Gesendet: Mittwoch, 09. August 2017 08:31
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] Optimizing searches across several indexes

Hi,

I have a table with a bunch of data (in json). I want to search on several 
values, each one is indexed. However, if I search for COND1 AND COND2, the 
query plan is simply

SEARCH TABLE data USING INDEX cond1Index (cond1=?)

Is this normal? I was hoping it could use the indexes somehow for both 
conditions.

Should I be creating indexes differently to cover two dimensions at once?
Perhaps with r*tree?

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