Tom Shaw <[EMAIL PROTECTED]> wrote: > 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); >
Try CREATE INDEX city_block_idx2 ON city_block(loc_id, start); -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
