Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch: > It is indeed possible to change the query so that SQLite uses rowid > lookups for the R-tree filter (INDEX 1). However, any likelihood on the > R-tree search expression still did not make any difference. Do you have > an example?
Try: --- CREATE TABLE t(id INTEGER PRIMARY KEY,x INTEGER,y INTEGER,z INTEGER); CREATE VIRTUAL TABLE i USING rtree(id,minx,maxx,miny,maxy); CREATE INDEX t_x ON t(x); --- EXPLAIN QUERY PLAN SELECT * FROM t INNER JOIN i USING(id) WHERE LIKELIHOOD(i.minX>=-81.08 AND i.maxX<=-80.58 AND i.minY>=35.00 AND i.maxY<=35.44, 0.999) -- 0.999 AND LIKELIHOOD(t.x=3, 0.001); -- 0.001 -> SEARCH TABLE t USING INDEX t_x (x=?) -> SCAN TABLE i VIRTUAL TABLE INDEX 1: --- SELECT * FROM t INNER JOIN i USING(id) WHERE LIKELIHOOD(i.minX>=-81.08 AND i.maxX<=-80.58 AND i.minY>=35.00 AND i.maxY<=35.44, 0.001) -- 0.001 AND LIKELIHOOD(t.x=3, 0.999); -- 0.999 -> SCAN TABLE i VIRTUAL TABLE INDEX 2:D0B1D2B3 -> SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?) --- Tested with SQLite 3.13.0 here, but IIRC newer versions behave the same. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
