Optimizing searches across several indexes

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Optimizing searches across several indexes

wmertens
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Optimizing searches across several indexes

Clemens Ladisch
Wout Mertens wrote:
> 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?

Yes.  A query can use only a single index per table.

> Should I be creating indexes differently to cover two dimensions at once?

Create an index on both colums:

  CREATE INDEX cond1and2Index ON data(cond1, cond2);

If you are not using equality comparisons on all but the last columns in
the index, you might need an expression index.  (With JSON, I guess you
are already doing this.)

> Perhaps with r*tree?

That would be useful mainly for multidimensional, numeric range queries
(e.g., "xColumn BETWEEN ? AND ? AND yColumn BETWEEN ? AND ?").


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Optimizing searches across several indexes

Hick Gunter
In reply to this post by wmertens
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:[hidden email]] Im Auftrag von Wout Mertens
Gesendet: Mittwoch, 09. August 2017 08:31
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Optimizing searches across several indexes

wmertens
This mailing list is so amazing :) Thank you both, everything is clear now!

On Wed, Aug 9, 2017 at 9:08 AM Hick Gunter <[hidden email]> 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:[hidden email]]
> Im Auftrag von Wout Mertens
> Gesendet: Mittwoch, 09. August 2017 08:31
> An: SQLite mailing list <[hidden email]>
> 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
> [hidden email]
> 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: [hidden email]
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...