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);
and there is an index on A for Latitude,Longitude. B is filled using INSERT INTO B SELECT ID,Latitude,Latitude,Longitude,Longitude FROM A; 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. It is slower than a direct (complete) search on A (SELECT COUNT(*) FROM A WHERE (Longitude > 5) AND (Longitude < 10);). Though this query is fast: SELECT COUNT(*) FROM A WHERE (ID IN (SELECT ID FROM B WHERE (FromLongitude > 5) AND (TillLongitude < 10))); Am I doing anything wrong or is the documentation not correct? Regards, Hartwig PS: I used for testing only 70 000 records but I do not think that it really matters. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
