Op 7 mrt 2015, om 19:24 heeft Wolfgang Enzinger het volgende geschreven: > 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 Hello, as yiou gave a very clear example of the case, I dare to reply.
Actually query one appears slightly faster, Searching the PK index is faster as that is always a COVERING index. From the secunsary indexes only a part oh the key is used. Note there is not much use on adding PK as second column in the additional indexes. It is there anyway a a pointer to the row. I agree that it is strange that the execution plan for the two queries is different, After EXISTS the optimizer might ignore the expression in the select part of the sub-query. And Query one looks better as it soes not mention any column names. Personally I'd write SELECT NULL instead of SELECT *. If speed matters instead of EXIST you can use IN and a list sub-query. This is superfast now: SELECT a1 FROM a WHERE a1 in (SELECT b.a1 FROM b INNER JOIN c USING(b1) WHERE c.c1=222); 0|0|0|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|1|SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?) 1|1|0|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?) Edzard Pasma
