List:General Discussion« Previous MessageNext Message »
From:Hank Date:April 19 2005 4:16am
Subject:Re: zip code search within x miles
View as plain text  
> Let's say you've got, oh, 2000 records to search through.
> You're gonna end up doing a JOIN with:
> 2,000 X 65,000 == 130,000,000 tuples (records/results).
> 130 MILLION tuples is *way* too many for your basic $20/month site.

I'd say take some easy shortcuts first... like limit the join to the
zip code table by the target state (which of course is indexed), then
add the one, two or at most three neighboring states, if you're near a
border.  Or just limit the join to all the neighboring states in one
shot). One, two, or three short running queries all in SQL is a
whole-lot better than adding un-normalized and redundant fields to the
source data table and populating it in a cron job and triggers.  Talk
about taking the long way around.

And if anyone is looking for a datafile with worldwide cities (about
2.7 million records) and their lat/longs (not zips, though), here's a
place to download it for free:

http://www.maxmind.com/app/worldcities

For $50, you can get the addition of population of each city.

-Hank

mysql, query
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