Hi Donald, I have a index on both lat and long, but please correct me if I'm wrong, but I think SQLite will only use a single index per SELECT. So only one of the index is in use. I think the EXPLAIN command confirms this for me.
I have not played with the cache size, but I have used VACUUM, and also recreated the database from a fresh dump of all the data (ie so no data has ever been removed). But from what I've read the rtree extension is designed for lat/long style problems, and thus would be perfect for my problem. However if you feel you can help me more by without the extension then the schema and query are below thanks Andrew CREATE TABLE Towers ( towerID INTEGER PRIMARY KEY NOT NULL, latitude REAL NOT NULL, longitude REAL NOT NULL, zoom INTEGER NOT NULL ); CREATE INDEX Towers_Latitude ON Towers (latitude); /* I was using these index, but without has not decreased the query time CREATE INDEX Towers_Longitude ON Towers (longitude); CREATE INDEX Towers_Zoom ON Towers (zoom); */ SELECT towerID AS i, latitude AS y, longitude AS x FROM towers WHERE longitude > -27.0263671875 AND longitude < 19.7314453125 AND latitude > 44.72599125467816 AND latitude < 64.6631165643883 ORDER BY zoom LIMIT 50; ----- Original Message ----- From: "Griggs, Donald" <[EMAIL PROTECTED]> Sent: Friday, June 13, 2008 5:15 PM Subject: Re: [sqlite] rtree extension - Windows Binary? > Hi Andrew, > > Maybe this goes without saying, but I guess you've already worked on the > "usual suspects" such as: > > -- An index for Lat and another for Lon. > -- Larger cache > -- EXPLAIN QUERY PLAN prefix to ensure that index used > -- One-time VACUUM has any effect? > -- ANALYZE > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users