[sqlite] Optimization Opportunity?

[Wolfgang Enzinger]

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