Where does one get "analyze"? -----Original Message----- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Saturday, February 17, 2007 5:25 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] retrieval speedup help requested
Try running: analyze city_loc after adding the index. RBS -----Original Message----- From: Tom Shaw [mailto:[EMAIL PROTECTED] Sent: 17 February 2007 22:16 To: sqlite-users@sqlite.org Subject: [sqlite] retrieval speedup help requested 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] ------------------------------------------------------------------------ ---- - ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- -------------------------------------------------------- NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------