Paul wrote:
> I've traced this issue down to the simplest test case:
>
> CREATE TABLE IF NOT EXISTS foo
> (
> id INTEGER,
> baz INTEGER,
> PRIMARY KEY(id)
> );
>
> CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id);
>
> CREATE TABLE IF NOT EXISTS bar
> (
> foo INTEGER,
> PRIMARY KEY(foo),
> FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE
> );
>
> WITH RECURSIVE
> cnt(x, y) AS (VALUES(1, 1) UNION ALL SELECT x + 1, x + 1 FROM cnt WHERE x <
> 200000)
> INSERT INTO foo(id, baz) SELECT x, y FROM cnt;
>
> WITH RECURSIVE
> cnt(x) AS (VALUES(1) UNION ALL SELECT x + 3 FROM cnt WHERE x < 50000)
> INSERT INTO bar SELECT x FROM cnt;
>
> This query takes too much time:
>
> SELECT id FROM foo WHERE baz = 10000 AND id IN (SELECT foo FROM bar) LIMIT 1;
EXPLAIN SELECT id FROM foo WHERE baz = 10000 AND id IN (SELECT foo FROM bar)
LIMIT 1;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 24 0 00 Start at 24
1 Integer 1 1 0 00 r[1]=1; LIMIT counter
2 OpenRead 2 3 0 k(3,,,) 02 root=3 iDb=0;
baz_foo_idx
3 Integer 10000 2 0 00 r[2]=10000
4 Once 0 6 0 00
5 OpenRead 3 4 0 1 00 root=4 iDb=0; bar
6 Rewind 3 22 0 00
7 Rowid 3 3 0 00 r[3]=rowid
8 IsNull 3 21 0 00 if r[3]==NULL goto
21
9 Once 1 11 0 00
10 OpenRead 4 4 0 1 00 root=4 iDb=0; bar
11 Rewind 4 21 0 00
12 Rowid 4 4 0 00 r[4]=rowid
13 IsNull 4 20 0 00 if r[4]==NULL
goto 20
14 SeekGE 2 20 2 3 00 key=r[2..4]
15 IdxGT 2 20 2 3 00 key=r[2..4]
16 IdxRowid 2 5 0 00 r[5]=rowid
17 ResultRow 5 1 0 00 output=r[5]
18 DecrJumpZero 1 22 0 00 if (--r[1])==0
goto 22
19 Next 2 15 0 00
20 NextIfOpen 4 12 0 00
21 NextIfOpen 3 7 0 00
22 Close 2 0 0 00
23 Halt 0 0 0 00
24 Transaction 0 0 3 0 01 usesStmtJournal=0
25 TableLock 0 2 0 foo 00 iDb=0 root=2 write=0
26 TableLock 0 4 0 bar 00 iDb=0 root=4 write=0
27 Goto 0 1 0 00
If I've understood this correctly, it's the equivalent of this pseudocode:
cursor c3 = scan bar
for each row in c3:
cursor c4 = scan bar
for each row in c4:
cursor c2 = search (10000, c3.rowid, c4.rowid) in baz_foo_idx
for each row in c2:
output c2.rowid
stop
This looks like a bug.
As a workaround, drop "id" from the index (the rowid is always part of
the index anyway).
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
