Hello !
Maybe this problem would be the reason of getting bad query plans when
joining views too.
Cheers !
On 14/04/17 08:03, Wolfgang Enzinger wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
