On 2/17/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:
Where does one get "analyze"?
man, its pretty much a standard SQL command (at least all the dbs I've used thus far). Check out http://www.sqlite.org/lang.html. Most questions answered.
-----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
-- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
