help with EXPLAIN QUERY PLAN

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

help with EXPLAIN QUERY PLAN

curmudgeon
Can anyone tell me why the detail column sometime states ‘USING INDEX ....’ and other time ‘USING COVERING INDEX ...’?

At first I thought USING COVERING INDEX implied the search was searching only the first m columns of an n column index (m less than n) but I’ve also seen USING COVERING INDEX when the query was searching all of the columns in the index.

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

Re: help with EXPLAIN QUERY PLAN

Lutz Horn-2
Ho,

Am 23.11.17 um 17:33 schrieb x:
> Can anyone tell me why the detail column sometime states ‘USING INDEX
> ....’ and other time ‘USING COVERING INDEX ...’?

See https://sqlite.org/queryplanner.html#_covering_indices

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

Re: [EXTERNAL] help with EXPLAIN QUERY PLAN

Hick Gunter
In reply to this post by curmudgeon
COVERING INDEX means that all required fields for the query can be read from the index alone, without accessing the row itself.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Donnerstag, 23. November 2017 17:34
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] help with EXPLAIN QUERY PLAN

Can anyone tell me why the detail column sometime states ‘USING INDEX ....’ and other time ‘USING COVERING INDEX ...’?

At first I thought USING COVERING INDEX implied the search was searching only the first m columns of an n column index (m less than n) but I’ve also seen USING COVERING INDEX when the query was searching all of the columns in the index.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] help with EXPLAIN QUERY PLAN

curmudgeon
Understand now. Thanks Lutz & Gunter.



________________________________
From: sqlite-users <[hidden email]> on behalf of Hick Gunter <[hidden email]>
Sent: Thursday, November 23, 2017 4:40:16 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] help with EXPLAIN QUERY PLAN

COVERING INDEX means that all required fields for the query can be read from the index alone, without accessing the row itself.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Donnerstag, 23. November 2017 17:34
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] help with EXPLAIN QUERY PLAN

Can anyone tell me why the detail column sometime states ‘USING INDEX ....’ and other time ‘USING COVERING INDEX ...’?

At first I thought USING COVERING INDEX implied the search was searching only the first m columns of an n column index (m less than n) but I’ve also seen USING COVERING INDEX when the query was searching all of the columns in the index.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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