Hi, I have the following schema:
CREATE TABLE a (id INT, partName TEXT, content BLOB, PRIMARY KEY(id, partName)); CREATE TABLE b (id, partName, content); CREATE VIEW aView AS SELECT a.id, a.partName, COALESCE(a.content, (SELECT b.content FROM b WHERE b.id=a.id AND b.partName=a.partName)) FROM a; CREATE INDEX bIndex ON b (id, partName); and the following query has a query plan that uses bIndex: EXPLAIN QUERY PLAN SELECT b.content FROM b WHERE b.id=? AND b.partName=? 0|0|0|SEARCH TABLE b USING INDEX bIndex (id=? AND partName=?) (~9 rows) However the subquery in the view yields a different query plan: EXPLAIN QUERY PLAN SELECT * FROM aView WHERE id=? AND partName=?; 0|0|0|SEARCH TABLE a USING INDEX sqlite_autoindex_a_1 (id=? AND partName=?) (~1 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SCAN TABLE b (~10000 rows) Is there a reason why the index is not used in the subquery? Best regards, Filip Navara _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
