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
