List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 19 2005 5:58pm
Subject:Re: zip code search within x miles
View as plain text  
Hank <heskin@stripped> wrote on 04/19/2005 01:33:51 PM:

> On 4/19/05, Keith Ivey <keith@stripped> wrote:
> > Also, the index on zip_code, latitude, and longitude doesn't
> > make sense. 
> 
> Yeah - I didn't even notice the indexes in the table def (I used my
> own existing zip code table).  That table def and query were obviously
> created by someone pretty green with SQL.
> 
> -Hank
> 
No, those indexes were intentional.  If you read the section of the manual 
on optimizing queries, you will encounter a page that mentions what are 
known as "covering indexes".  The advantage to a covering index is that if 
your data is numeric and in the index, the engine can read the data 
DIRECTLY from the index and completely skip all of the disk operations 
required to read the information from the data file.

That index is a covering index and will make any query looking for just 
lat and long against a zip code extremely fast because the engine will not 
need to read the data file to get at the lat and long value because they 
are already in the index.

from: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
++++++++++++++++++++++++++
In some cases, a query can be optimized to retrieve values without 
consulting the data rows. If a query uses only columns from a table that 
are numeric and that form a leftmost prefix for some key, the selected 
values may be retrieved from the index tree for greater speed:

SELECT key_part3 FROM tbl_name WHERE key_part1=1
++++++++++++++++++++++++++

I guess the person who wrote that query wasn't so green after all, eh? 
(BTW, I was not the author of the OP's query although there is a lot of 
irony in this reply ;-D  )

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
zip code search within x milesScott Haneda16 Apr
  • RE: zip code search within x milesgunmuse16 Apr
  • Re: zip code search within x milesRobert Dunlop16 Apr
    • RE: zip code search within x milesScott Johnson19 Apr
  • Re: zip code search within x milesGreg Donald16 Apr
  • Re: zip code search within x milesScott Gifford16 Apr
    • RE: zip code search within x milesgunmuse16 Apr
      • Re: zip code search within x milesJeff Kolber16 Apr
        • Re: zip code search within x milesScott Gifford17 Apr
          • Re: zip code search within x milesJeff Kolber20 Apr
  • Re: zip code search within x milesRichard Lynch19 Apr
    • Re: zip code search within x milesparis lundis19 Apr
      • Re: zip code search within x milesHank19 Apr
    • Re: zip code search within x milesHank19 Apr
      • Re: zip code search within x milesRichard Lynch25 Apr
  • Re: zip code search within x milesEamon Daly19 Apr
    • RE: zip code search within x milesScott Johnson19 Apr
  • Re: zip code search within x milesEamon Daly19 Apr
    • Re: zip code search within x milesHank19 Apr
      • Re: zip code search within x milesKeith Ivey19 Apr
        • Re: zip code search within x milesHank19 Apr
          • Re: zip code search within x milesSGreen19 Apr
            • Re: zip code search within x milesHank19 Apr
              • Re: zip code search within x milesRichard Lynch25 Apr
            • Re: zip code search within x milesJeremy Cole29 Apr
          • Problem detected this morning.Fredrick Bartlett5 Aug
            • Re: Problem detected this morning.Gleb Paharenko7 Aug
        • Re: zip code search within x milesScott Haneda19 Apr
      • Re: zip code search within x milesRichard Lynch25 Apr
        • RE: zip code search within x milesgunmuse25 Apr