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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
