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 < 20000) INSERT INTO foo(id, baz) SELECT x, y FROM cnt;
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x + 1 FROM cnt WHERE x <
10000) 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;
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
