Hello, given the following:
------------------------ CREATE TABLE x( pk INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE y( fk INTEGER REFERENCES x(pk), flags INTEGER ); CREATE INDEX yy ON y(fk); CREATE VIEW z AS SELECT fk, (flags&1) AS odd, (flags&2)>>1 AS even, (flags&4)>>2 AS prime FROM y; INSERT INTO x(pk,description) VALUES (1,'one'),(2,'two'),(3,'three'),(4,'four'); INSERT INTO y(fk,flags) VALUES (1,1|0|0),(2,0|2|4),(3,1|0|4),(4,0|2|0); ------------------------ Now using the VIEW z in a JOIN results in a full table scan on TABLE y despite a WHERE clause and an appropriate INDEX: EXPLAIN QUERY PLAN SELECT x.pk,z.odd,z.even,z.prime FROM x LEFT JOIN z ON x.pk=z.fk WHERE x.pk=2; 1|0|0|SCAN TABLE y 0|0|0|SEARCH TABLE x USING INTEGER PRIMARY KEY (rowid=?) 0|1|1|SCAN SUBQUERY 1 Bypassing the VIEW however uses INDEX yy: EXPLAIN QUERY PLAN SELECT x.pk,(y.flags&1) AS odd,(y.flags&2)>>1 AS even,(y.flags&4)>>2 AS prime FROM x LEFT JOIN y ON x.pk=y.fk WHERE x.pk=2; 0|0|0|SEARCH TABLE x USING INTEGER PRIMARY KEY (rowid=?) 0|1|1|SEARCH TABLE y USING INDEX yy (fk=?) Unless I'm missing something, I think there is a potential optimization opportunity. Identical results with SQLite versions 3.13, 3.17 and 3.18. Cheers, Wolfgang _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users