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:[email protected]] Im Auftrag von Wout Mertens Gesendet: Mittwoch, 09. August 2017 08:31 An: SQLite mailing list <[email protected]> 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 [email protected] 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: [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
