This mailing list is so amazing :) Thank you both, everything is clear now!
On Wed, Aug 9, 2017 at 9:08 AM Hick Gunter <h...@scigames.at> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
