CODEWITHSUNDEEP
sqlsqliteindexingsql-order-by
Naryc
Naryc

Reputation: 211

Can sqlite `order by` and `where` clause use the Index together

Create a table

CREATE TABLE Shares(
    shareId TEXT PRIMARY KEY NOT NULL,
    fromId INTEGER NOT NULL,
    toId INTEGER NOT NULL,
    time INTEGER NOT NULL);

And create the index

CREATE INDEX Shares_time_toId_fromId ON Shares(time, toId, fromId);

Then insert some rows

insert into Shares(shareId, fromId, toId, time) values(1,1,1,1);
insert into Shares(shareId, fromId, toId, time) values(2,2,2,2);
insert into Shares(shareId, fromId, toId, time) values(3,3,3,3);
insert into Shares(shareId, fromId, toId, time) values(4,3,3,4);
insert into Shares(shareId, fromId, toId, time) values(5,3,3,5);

use explain to show

explain select * from Shares where toId=3 and fromId=3 order by time desc;

the result is 

0|Init|0|20|0||00|
1|Noop|0|0|0||00|
2|OpenRead|0|2|0|4|00|
3|OpenRead|2|4|0|k(4,nil,nil,nil,nil)|00|
4|Last|2|17|1|0|00|
5|IdxRowid|2|1|0||00|
6|Seek|0|1|0||00|
7|Column|2|1|2||00|
8|Ne|3|16|2|(BINARY)|54|
9|Column|2|2|4||00|
10|Ne|3|16|4|(BINARY)|54|
11|Column|0|0|5||00|
12|Copy|4|6|0||00|
13|Copy|2|7|0||00|
14|Column|2|0|8||00|
15|ResultRow|5|4|0||00|
16|Prev|2|5|0||01|
17|Close|0|0|0||00|
18|Close|2|0|0||00|
19|Halt|0|0|0||00|
20|Transaction|0|0|2|0|01|
21|TableLock|0|2|0|Shares|00|
22|Integer|3|3|0||00|
23|Goto|0|1|0||00|

It seems the query use the index to finish the job. But I don't know whether it uses all three index or just use time to do the index. I hope the indices could be all used to give the performance enhancement.

Upvotes: 1

Views: 1960

Answers (1)

CL.
CL.

Reputation: 180080

To find out how the query is executed, don't use EXPLAIN but EXPLAIN QUERY PLAN:

explain query plan select * from Shares where toId=3 and fromId=3 order by time desc;
0|0|0|SCAN TABLE Shares USING INDEX Shares_time_toId_fromId

In this query, the toId and fromId values are read from the index, but this does not matter because the actual table has to be read anyway to get the shareId value.

If the query did not try to read the shareId column, or if the shareId column had type INTEGER so that it would be an alias for the rowid and thus be part of the index, the separate table lookup step would not be needed.

(Note: the latest version of the sqlite3 tool formats the EXPLAIN output better.)

Upvotes: 3

Related Questions