[sqlite] retrieval speedup help requested

[Tom Shaw]

In sqlite 3 I have two tables. city_loc has 156865 entries and 
city_block has 1874352 entries:

CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region 
TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT);
CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER 
UNSIGNED NOT NULL, loc_id INTEGER NOT NULL);

And my retrieval is but it is slow (6 seconds!):
SELECT cc, region, city, postalCode, lat, lon, areaCode FROM 
city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end;

I tried using:
CREATE INDEX city_block_idx ON city_block (start,end);

but it did not appear to speedup anything but it did use up a lot of space.

Any suggestions on how to speedup retrievals?  All help is appreciated.

TIA

Tom


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------