Jens Miltner wrote:
Is there any way to improve the ORDER BY performance for joined
queries? From your answer that the intermediate results are sorted, I
take it no index won't ever be used when using ORDER BY with a join
query?
Is there a way to rewrite the queries so we don't take the penalty of
sorting without an index?
In our case, the 'bar' table may have several 100,000 entries and the
'foo' table probably has much less (say order of thousand).
A minimal query (i.e. one where we don't return a minimal amount of
data, so the pure data shuffling doesn't get in the way) with a 'bar'
table with 250,000 entries and a 'foo' table with around 10 entries
(so the 250,000 entries in the 'bar' table relate to only a few
records in the 'foo' table), a query like the above takes 10-20
minutes to run (depending on the ORDER BY clauses used)!
(Side-note: The table does have quite a few columns and apparently,
the amount of data per row also massively affects the performance...
when I tried with the same table with not all columns filled in - i.e.
less data - performance was much better. I also tried increasing the
page size, hoping that less paging would have happen, but this didn't
really make a noticeable difference)
I would really appreciate any hints on how to improve performance with
this kind of setup...
Jens,
If you create an index on your bar.something column, it will be used to
do the ordering. The log below shows how this query will be executed
before and after creating this index.
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> CREATE TABLE foo (id integer primary key, name text);
sqlite> CREATE TABLE bar (id integer primary key, foo_id integer,
something text
);
sqlite>
sqlite> CREATE INDEX bar_idx on bar(foo_id, something);
sqlite>
sqlite> .explain on
sqlite> explain SELECT
...> foo.name AS name,
...> bar.something AS something
...> FROM
...> bar
...> LEFT JOIN
...> foo ON foo.id=bar.foo_id
...> ORDER BY
...> bar.something
...> ;
addr opcode p1 p2 p3
---- -------------- ---------- ----------
---------------------------------
0 OpenVirtual 2 3 keyinfo(1,BINARY)
1 Goto 0 40
2 Integer 0 0
3 OpenRead 0 3
4 SetNumColumns 0 3
5 Integer 0 0
6 OpenRead 1 2
7 SetNumColumns 1 2
8 Rewind 0 26
9 MemInt 0 1
10 Column 0 1
11 MustBeInt 1 22
12 NotExists 1 22
13 MemInt 1 1
14 Column 1 1
15 Column 0 2
16 MakeRecord 2 0
17 Column 0 2
18 Sequence 2 0
19 Pull 2 0
20 MakeRecord 3 0
21 IdxInsert 2 0
22 IfMemPos 1 25
23 NullRow 1 0
24 Goto 0 13
25 Next 0 9
26 Close 0 0
27 Close 1 0
28 OpenPseudo 3 0
29 SetNumColumns 3 2
30 Sort 2 38
31 Integer 1 0
32 Column 2 2
33 Insert 3 0
34 Column 3 0
35 Column 3 1
36 Callback 2 0
37 Next 2 31
38 Close 3 0
39 Halt 0 0
40 Transaction 0 0
41 VerifyCookie 0 3
42 Goto 0 2
43 Noop 0 0
sqlite> .explain off
sqlite> explain query plan SELECT
...> foo.name AS name,
...> bar.something AS something
...> FROM
...> bar
...> LEFT JOIN
...> foo ON foo.id=bar.foo_id
...> ORDER BY
...> bar.something
...> ;
0|0|TABLE bar
1|1|TABLE foo USING PRIMARY KEY
sqlite>
sqlite> create index bar_some on bar(something);
sqlite>
sqlite> .explain on
sqlite> explain SELECT
...> foo.name AS name,
...> bar.something AS something
...> FROM
...> bar
...> LEFT JOIN
...> foo ON foo.id=bar.foo_id
...> ORDER BY
...> bar.something
...> ;
addr opcode p1 p2 p3
---- -------------- ---------- ----------
---------------------------------
0 Noop 0 0
1 Goto 0 31
2 Integer 0 0
3 OpenRead 0 3
4 SetNumColumns 0 3
5 Integer 0 0
6 OpenRead 3 5 keyinfo(1,BINARY)
7 Integer 0 0
8 OpenRead 1 2
9 SetNumColumns 1 2
10 Rewind 3 27
11 RowKey 3 0
12 IdxIsNull 0 26
13 IdxRowid 3 0
14 MoveGe 0 0
15 MemInt 0 1
16 Column 0 1
17 MustBeInt 1 23
18 NotExists 1 23
19 MemInt 1 1
20 Column 1 1
21 Column 0 2
22 Callback 2 0
23 IfMemPos 1 26
24 NullRow 1 0
25 Goto 0 19
26 Next 3 11
27 Close 0 0
28 Close 3 0
29 Close 1 0
30 Halt 0 0
31 Transaction 0 0
32 VerifyCookie 0 4
33 Goto 0 2
34 Noop 0 0
sqlite> .explain off
sqlite> explain query plan SELECT
...> foo.name AS name,
...> bar.something AS something
...> FROM
...> bar
...> LEFT JOIN
...> foo ON foo.id=bar.foo_id
...> ORDER BY
...> bar.something
...> ;
0|0|TABLE bar WITH INDEX bar_some
1|1|TABLE foo USING PRIMARY KEY
sqlite>
With the index on bar.something SQLite can scan the bar table in the
order required, and for each bar record it will use the primary key
index on the foo table to join the appropriate record from that table.
HTH
Dennis Cote
