Am 09.12.2013 um 10:06 schrieb Clemens Ladisch <clem...@ladisch.de>:
> skywind mailing lists wrote: >> Assume I have the following tables: >> >> CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude); >> CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, >> FromLongitude,TillLongitude); >> >> According to the RTree documentation this query should be fast (demo_data / >> demo_index example): >> >> SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND (FromLongitude > 5) AND >> (TillLongitude < 10); >> >> Actually, the query is pretty slow. > > According to the EXPLAIN QUERY PLAN output (<http://www.sqlite.org/eqp.html>), > it is fast: > > 0|0|1|SCAN TABLE B VIRTUAL TABLE INDEX 2:EcCd > 0|1|0|SEARCH TABLE A USING INTEGER PRIMARY KEY (rowid=?) > > To you get the same output for EXPLAIN QUERY PLAN? > If not, what SQLite version are you using? > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Hi Clemens, I am using SQLite 3.7.13. If I do not run ANALYZE my query plan is the same (I rebuild the tables). BUT my query plan is different after ANALYZE: 0|0|0|SCAN TABLE A (~74067 rows) 0|1|1|SCAN TABLE B VIRTUAL TABLE INDEX 1: (~0 rows) The problem seems to be (~0 rows) for TABLE B. There are definitely 74067 rows in table B. Therefore, I conclude that ANALYZE is not able to analyze RTree tables correctly and therefore the wrong query plan is chosen. Regards, Hartwig _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
