Jens Miltner wrote:
I have a schema similar to this:
CREATE TABLE foo (id integer primary key, name text);
CREATE TABLE bar (id integer primary key, foo_id integer, something
text);
CREATE INDEX bar_idx on bar(foo_id, something);
When I run a query like
SELECT
foo.id AS foo_id,
bar.id AS bar_id
FROM
bar
LEFT JOIN
foo ON foo.id=bar.foo_id
ORDER BY
bar.something
;
sqlite will only use the primary key index of foo
(as the output of 'explain query plan' shows:
0|0|TABLE bar
1|1|TABLE foo USING PRIMARY KEY
)
If I try to disable the foo primary key index by prefixing foo.id with
a '+' sign, no index will be used.
I'd have expected the bar_idx index to be used for the ORDER BY
clause? Or is this some unreasonable assumption?
Is there a way to enforce this?
Thanks,
</jum>
Jens,
Your query is not using any index to do the order by clause, it is
sorting the intermediate results.
There is no need to join the foo table in your query since the foo_id is
available in the bar table. The following is equivalent:
SELECT
foo_id,
bar.id AS bar_id
FROM
bar
ORDER BY
bar.something
;
It still does a sort as shown by the explain output
sqlite> .explain on
sqlite> explain SELECT
...> foo_id,
...> bar.id AS bar_id
...> FROM
...> bar
...> ORDER BY
...> bar.something
...> ;
addr opcode p1 p2 p3
---- -------------- ---------- ----------
---------------------------------
0 OpenVirtual 1 3 keyinfo(1,BINARY)
1 Goto 0 28
2 Integer 0 0
3 OpenRead 0 3
4 SetNumColumns 0 3
5 Rewind 0 15
6 Column 0 1
7 Rowid 0 0
8 MakeRecord 2 0
9 Column 0 2
10 Sequence 1 0
11 Pull 2 0
12 MakeRecord 3 0
13 IdxInsert 1 0
14 Next 0 6
15 Close 0 0
16 OpenPseudo 2 0
17 SetNumColumns 2 2
18 Sort 1 26
19 Integer 1 0
20 Column 1 2
21 Insert 2 0
22 Column 2 0
23 Column 2 1
24 Callback 2 0
25 Next 1 19
26 Close 2 0
27 Halt 0 0
28 Transaction 0 0
29 VerifyCookie 0 3
30 Goto 0 2
31 Noop 0 0
sqlite> .explain off
sqlite> explain query plan SELECT
...> foo_id,
...> bar.id AS bar_id
...> FROM
...> bar
...> ORDER BY
...> bar.something
...> ;
0|0|TABLE bar
sqlite>
To use the index to do the oder by the index must start with the columns
used to order the output. If you create an index on the bar.something
column then it will be used to scan the bar table in order rather than
from start to finish by primary key id.
sqlite> create index bar_someting on bar(something);
sqlite> .explain on
sqlite> explain SELECT
...> foo_id,
...> bar.id AS bar_id
...> FROM
...> bar
...> ORDER BY
...> bar.something
...> ;
addr opcode p1 p2 p3
---- -------------- ---------- ----------
---------------------------------
0 Noop 0 0
1 Goto 0 19
2 Integer 0 0
3 OpenRead 0 3
4 SetNumColumns 0 3
5 Integer 0 0
6 OpenRead 2 5 keyinfo(1,BINARY)
7 Rewind 2 16
8 RowKey 2 0
9 IdxIsNull 0 15
10 IdxRowid 2 0
11 MoveGe 0 0
12 Column 0 1
13 Rowid 0 0
14 Callback 2 0
15 Next 2 8
16 Close 0 0
17 Close 2 0
18 Halt 0 0
19 Transaction 0 0
20 VerifyCookie 0 4
21 Goto 0 2
22 Noop 0 0
sqlite> .explain off
sqlite>
sqlite> explain query plan SELECT
...> foo_id,
...> bar.id AS bar_id
...> FROM
...> bar
...> ORDER BY
...> bar.something
...> ;
0|0|TABLE bar WITH INDEX bar_someting
sqlite>
HTH
Dennis Cote
