On 2/17/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:
Thanks. AFAIK it's not available in sybase, and that's my only sql background.
I guessed as much. What ANALYZE does (informal explanation follows) is that it updates all the internal statistics and indexes that SQLite needs for its queries, kinda like kick-starting the indexing process. Nevertheless, there is probably something funky with your query itself. Looking at it in detail, you have 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 ); SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; so, I didn't know what a NATURAL JOIN was. Went, looked it up **. Seems like it joins on like named and typed columns. Ok. So, in other words, you are doing SELECT cb.cc AS cc, cb.region AS region, cb.city AS city, cb.postalCode AS postalCode, cb.lat AS lat, cb.lon AS lon, cb.areaCode AS areaCode FROM city_block cb JOIN city_loc cl ON cb.loc_id = cl.loc_id WHERE $ipnum BETWEEN cl.start AND cl.end and you have made a composite index on cl.start and cl.end. Try an index on cl.loc_id as well so that the JOIN is done efficiently. ** by the way, asktom's advice with NATURAL JOINs... "it's ambiguous at best and leaves you open to problems if columns get added or renamed, no more than two tables can be joined using this method, and it gives you little control over the specifics of a join if columns join across the tables in an unusual way. Tom's advice with NATURAL JOINs- forget that they exist." I would agree... it just seems a better practice to spell things out explicitly in programs, not just for the benefit for others but perhaps for one's own benefit when you come back to look at your own code 6 months later.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Saturday, February 17, 2007 8:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] retrieval speedup help requested 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] ------------------------------------------------------------------------ ----- -------------------------------------------------------- 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.
-- 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] -----------------------------------------------------------------------------
