I have a database with the following schema: CREATE TABLE mapelements (ID INTEGER PRIMARY KEY NOT NULL, TYPECODE INTEGER,NAME TEXT,LAT INTEGER,LON INTEGER,SWLAT INTEGER,SWLON INTEGER,NELAT INTEGER,NELON INTEGER,POLY BLOB,TILE INTEGER); CREATE VIRTUAL TABLE mapelements_rtree USING rtree(ID,SWLAT,NELAT,SWLON,NELON); CREATE TABLE "mapelements_rtree_node"(nodeno INTEGER PRIMARY KEY, data BLOB); CREATE TABLE "mapelements_rtree_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER); CREATE TABLE "mapelements_rtree_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER); CREATE INDEX mapelements_bbox ON mapelements(SWLAT,NELAT,SWLON,NELON); CREATE INDEX mapelements_latlon ON mapelements(LAT,LON); CREATE INDEX mapelements_name ON mapelements(NAME COLLATE NOCASE); CREATE INDEX mapelements_nelat ON mapelements(NELAT); CREATE INDEX mapelements_nelon ON mapelements(NELON); CREATE INDEX mapelements_swlat ON mapelements(SWLAT); CREATE INDEX mapelements_swlon ON mapelements(SWLON); CREATE INDEX mapelements_tile ON mapelements(TILE);
The database contains 2653693 rows, and is approximately 830MBytes big without the rtree index, and approximately 970MBytes with the rtree index. I have created the rtree index with the following script: CREATE VIRTUAL TABLE mapelements_rtree USING rtree(ID,SWLAT,NELAT,SWLON,NELON); INSERT INTO mapelements_rtree SELECT ID,SWLAT,NELAT,SWLON,NELON FROM mapelements; analyze; vacuum; The following query is very quick, it returns 20 rows within a small fraction of a second: select * from mapelements_rtree where NELAT>=79000000 and SWLAT<=80000000 and NELON>=79000000 and SWLON<=80000000; The following query, however, takes a long time (almost half a minute): select * from mapelements,mapelements_rtree where mapelements_rtree.NELAT>=79000000 and mapelements_rtree.SWLAT<=80000000 and mapelements_rtree.NELON>=79000000 and mapelements_rtree.SWLON<=80000000 and mapelements.ID=mapelements_rtree.ID; This is basically how the rtree documentation suggests to perform selects. Why is this query so slow, and what can I do to fix/workaround this? Thanks, Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
