Hi dev team, not sure if this is actually a useful hint, but ...
CREATE TABLE a(a1 INTEGER PRIMARY KEY); INSERT INTO a VALUES (1),(2),(3); CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY); INSERT INTO b VALUES (1,11),(2,22),(3,33); CREATE UNIQUE INDEX b_ui ON b(a1,b1); CREATE TABLE c(b1 INTEGER REFERENCES b(b1),c1 INTEGER PRIMARY KEY,c2 TEXT); INSERT INTO c VALUES (11,111,'a'),(22,222,'b'),(33,333,'c'); CREATE UNIQUE INDEX c_ui ON c(b1,c1); ANALYZE; Query 1: EXPLAIN QUERY PLAN SELECT a1 FROM a WHERE EXISTS(SELECT * FROM b INNER JOIN c USING(b1) WHERE b.a1=a.a1 AND c.c1=222); selectid order from detail 0 0 0 SCAN TABLE a 0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 1 1 0 1 SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?) 1 1 0 SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?) Query 2: EXPLAIN QUERY PLAN SELECT a1 FROM a WHERE EXISTS(SELECT c1 FROM b INNER JOIN c USING(b1) WHERE b.a1=a.a1 AND c.c1=222); selectid order from detail 0 0 0 SCAN TABLE a 0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 1 1 0 0 SEARCH TABLE b USING COVERING INDEX b_ui (a1=?) 1 1 1 SEARCH TABLE c USING COVERING INDEX c_ui (b1=?) Note that the only difference between the two is "SELECT *" vs. "SELECT c1" within the EXISTS-block. The result is the same in both cases, however the second query uses COVERING INDEXes which should be more efficient (as far as I know). HTH; and sorry for the noise if not. Wolfgang
