I personally would use "... EXISTS ( SELECT 1 ...", which requires no extra columns to be acessed at all.
-----Urspr?ngliche Nachricht----- Von: Wolfgang Enzinger [mailto:sqlite at enzinger.net] Gesendet: Samstag, 07. M?rz 2015 19:25 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Optimization Opportunity? 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
