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

Reply via email to