Ken wrote:
> select * from foo
> where rowid =
> (select rowid from foo
> where parent_id = 1
> and child_id = 2
> group by parent_id, child_id
> having revision = max(revision))
>
> sqlite> explain query plan
> ...> select * from foo
> ...> where rowid =
> ...> (select rowid from foo
> ...> where parent_id = 1
> ...> and child_id = 2
> ...> group by parent_id, child_id
> ...> having revision = max(revision))
> ...> ;
> order|from|detail
> 0|0|TABLE foo USING PRIMARY KEY
> 0|0|TABLE foo WITH INDEX FOO_IDX1 ORDER BY
>
>
> Would the above be faster due to the use of the PRIMARY key access ?
>
Ken,
I'm not sure about the general case, but in this case I suspect not
because of SQLite's index data optimization.
If all the required columns can be returned from an index, then SQLite
will pull the data from the index and never actually reference the base
table. That condition is true here since all the column are in the index.
I have copied the explain output for both queries below. My query looks
more direct and uses the index optimization to return the data. I would
have to measure the actual execution time to be sure, but I suspect it
will execute faster as well.
C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.5.6
Enter ".help" for instructions
sqlite>
sqlite> CREATE TABLE if not exists FOO (
...> PARENT_ID INTEGER,
...> CHILD_ID INTEGER,
...> REVISION INTEGER
...> );
sqlite>
sqlite> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION);
sqlite>
sqlite> .explain
sqlite> explain
...> select * from foo
...> where parent_id = :parentId
...> and child_id = :childId
...> and revision =
...> (select max(revision) from foo
...> where parent_id = :parentId
...> and child_id = :childId)
...> ;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 explain
select * from foo
where parent_id = :parentId
and child_id = :childId
and revision =
(select max(revision) from foo
where parent_id = :parentId
and child_id = :childId)
; 00
1 Goto 0 48 0 00
2 OpenRead 2 3 0 keyinfo(3,BINARY,BINARY) 00
3 SetNumColumns 2 4 0 00
4 Variable 1 2 0 :parentId 00
5 IsNull 2 46 0 00
6 Variable 2 3 0 :childId 00
7 IsNull 3 46 0 00
8 If 7 36 0 00
9 Integer 1 7 0 00
10 Null 0 8 0 00
11 Integer 1 9 0 00
12 MustBeInt 9 0 0 00
13 IfZero 9 36 0 00
14 Null 0 11 0 00
15 Null 0 10 0 00
16 OpenRead 3 3 0 keyinfo(3,BINARY,BINARY) 00
17 SetNumColumns 3 4 0 00
18 Variable 1 13 0 :parentId 00
19 IsNull 13 30 0 00
20 Variable 2 14 0 :childId 00
21 IsNull 14 30 0 00
22 MakeRecord 13 2 12 dddb 00
23 MoveLe 3 30 12 00
24 IdxLT 3 30 12 00
25 Column 3 2 17 00
26 CollSeq 0 0 0 collseq(BINARY) 00
27 AggStep 0 17 10 max(1) 01
28 Goto 0 30 0 00
29 Prev 3 24 0 00
30 Close 3 0 0 00
31 AggFinal 10 1 0 max(1) 00
32 SCopy 10 17 0 00
33 Move 17 8 0 00
34 AddImm 9 -1 0 00
35 IfZero 9 36 0 00
36 SCopy 8 4 0 00
37 IsNull 4 46 0 00
38 MakeRecord 2 3 1 dddb 00
39 MoveGe 2 46 1 00
40 IdxGE 2 46 1 01
41 Column 2 0 18 00
42 Column 2 1 19 00
43 Column 2 2 20 00
44 ResultRow 18 3 0 00
45 Next 2 40 0 00
46 Close 2 0 0 00
47 Halt 0 0 0 00
48 Transaction 0 0 0 00
49 VerifyCookie 0 2 0 00
50 TableLock 0 2 0 FOO 00
51 Goto 0 2 0 00
sqlite> explain query plan
...> select * from foo
...> where parent_id = :parentId
...> and child_id = :childId
...> and revision =
...> (select max(revision) from foo
...> where parent_id = :parentId
...> and child_id = :childId)
...> ;
orde from deta
---- ------------- ----
0 0 TABLE foo WITH INDEX FOO_IDX1
0 0 TABLE foo WITH INDEX FOO_IDX1 ORDER BY
sqlite>
sqlite>
sqlite>
sqlite> explain
...> select * from foo
...> where rowid =
...> (select rowid from foo
...> where parent_id = 1
...> and child_id = 2
...> group by parent_id, child_id
...> having revision = max(revision))
...> ;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 explain
select * from foo
where rowid =
(select rowid from foo
where parent_id = 1
and child_id = 2
group by parent_id, child_id
having revision = max(revision))
; 00
1 Goto 0 66 0 00
2 OpenRead 0 2 0 00
3 SetNumColumns 0 3 0 00
4 If 2 57 0 00
5 Integer 1 2 0 00
6 Null 0 3 0 00
7 Integer 1 4 0 00
8 MustBeInt 4 0 0 00
9 IfZero 4 57 0 00
10 Noop 0 0 0 00
11 Integer 0 9 0 00
12 Integer 0 8 0 00
13 Goto 0 29 0 00
14 Integer 1 9 0 00
15 Return 0 0 0 00
16 IfPos 8 18 0 00
17 Return 0 0 0 00
18 AggFinal 7 1 0 max(1) 00
19 Ne 7 17 6 collseq(BINARY) 6c
20 SCopy 5 15 0 00
21 Move 15 3 0 00
22 AddImm 4 -1 0 00
23 IfZero 4 14 0 00
24 Return 0 0 0 00
25 Null 0 5 0 00
26 Null 0 6 0 00
27 Null 0 7 0 00
28 Return 0 0 0 00
29 Gosub 0 25 0 00
30 OpenRead 3 3 0 keyinfo(3,BINARY,BINARY) 00
31 SetNumColumns 3 4 0 00
32 Integer 1 17 0 00
33 IsNull 17 55 0 00
34 Integer 2 18 0 00
35 IsNull 18 55 0 00
36 MakeRecord 17 2 16 dddb 00
37 MoveGe 3 55 16 00
38 IdxGE 3 55 16 01
39 Column 3 0 12 00
40 Column 3 1 13 00
41 Ne 11 43 13 collseq(BINARY) 10
42 Eq 10 48 12 collseq(BINARY) 10
43 Move 12 10 0 00
44 Move 13 11 0 00
45 Gosub 0 16 0 00
46 IfPos 9 57 0 00
47 Gosub 0 25 0 00
48 Column 3 2 15 00
49 CollSeq 0 0 0 collseq(BINARY) 00
50 AggStep 0 15 7 max(1) 01
51 IdxRowid 3 5 0 00
52 Column 3 2 6 00
53 Integer 1 8 0 00
54 Next 3 38 0 00
55 Close 3 0 0 00
56 Gosub 0 16 0 00
57 SCopy 3 1 0 00
58 MustBeInt 1 64 1 00
59 NotExists 0 64 1 00
60 Column 0 0 21 00
61 Column 0 1 22 00
62 Column 0 2 23 00
63 ResultRow 21 3 0 00
64 Close 0 0 0 00
65 Halt 0 0 0 00
66 Transaction 0 0 0 00
67 VerifyCookie 0 2 0 00
68 TableLock 0 2 0 FOO 00
69 Goto 0 2 0 00
sqlite> explain query plan
...> select * from foo
...> where rowid =
...> (select rowid from foo
...> where parent_id = 1
...> and child_id = 2
...> group by parent_id, child_id
...> having revision = max(revision))
...> ;
orde from deta
---- ------------- ----
0 0 TABLE foo USING PRIMARY KEY
0 0 TABLE foo WITH INDEX FOO_IDX1 ORDER BY
sqlite>
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
