To add to that, EXPLAIN QUERY PLAN shows that covering index will be used:
sqlite> EXPLAIN QUERY PLAN SELECT id FROM foo WHERE baz = 10000 AND id IN
(SELECT foo FROM bar) LIMIT 1;
selectid order from detail
---------- ---------- ----------
------------------------------------------------------------------------------
0 0 0 SEARCH TABLE foo USING COVERING INDEX
baz_foo_idx (baz=? AND id=? AND rowid=??)
It is not clear to me, what query algorithm is doing. It seems like it iterates
through bar and for each row of bar it performs unindexed cross-search in the
foo.
However, according to EXPLAIN, it should iterate over the baz_foo_idx index and
perform indexed cross-searches in the bar.
> 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;
>
> SELECT id FROM foo WHERE baz = 99999 AND id IN (SELECT foo FROM bar) LIMIT 0,
> 10;
>
>
> This query takes too much time:
>
> SELECT id FROM foo WHERE baz = 10000 AND id IN (SELECT foo FROM bar) LIMIT
> 1;
>
>
> It seems like execution time is a function of baz:
>
> sqlite> .timer on
> sqlite> SELECT id FROM foo WHERE baz = 10000 AND id IN (SELECT foo FROM bar)
> LIMIT 1;
> id
> ----------
> 10000
> Run Time: real 14.839 user 14.836000 sys 0.000000
> sqlite> SELECT id FROM foo WHERE baz = 1000 AND id IN (SELECT foo FROM bar)
> LIMIT 1;
> id
> ----------
> 1000
> Run Time: real 1.577 user 1.576000 sys 0.000000
> sqlite> SELECT id FROM foo WHERE baz = 100 AND id IN (SELECT foo FROM bar)
> LIMIT 1;
> id
> ----------
> 100
> Run Time: real 0.232 user 0.232000 sys 0.000000
> sqlite> SELECT id FROM foo WHERE baz = 10 AND id IN (SELECT foo FROM bar)
> LIMIT 1;
> id
> ----------
> 10
> Run Time: real 0.036 user 0.036000 sys 0.000000
> sqlite> SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar)
> LIMIT 1;
> id
> ----------
> 1
> Run Time: real 0.001 user 0.000000 sys 0.000000
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
