CODEWITHSUNDEEP
mysqlsearchzipcode
rogerb
rogerb

Reputation: 251

Fast Zipcode search in MySql

I have a MySQL table of Florists and I'm trying to write a search function which will take Zipcode as an input and find all Florists who deliver to that zipcode. Each florist record in my table has 3 items

Lets say I want to find all florists who deliver to "02108". What is the best way to implement this search in MySQL?

I was thinking

  1. Exact Match search - but this would require every search to scan the entire table. Inefficient?

  2. Haversine search + Exact Match - Use the latitutde/longitude value to narrow down the search to a 15 mile radius

    MySQL Great Circle Distance (Haversine formula) And then perform the Exact Match within these results.

  3. Something else?

Would appreciate your thoughts and input.

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

The proper way is to represent the list of zip codes as a junction table, with one row per florist and one row per zip code. So, your first florist might have these rows:

FloristId   Zip
1           02215
1           02108
1           02109
1           02150
1           02143
1           02144
1           02145

Then, build an index on the zip column. Then you can use a simple join to get florists who deliver to a particular zip code.

You can have a reference table to look up the primary town for a zip code. The US Census Bureau has a mapping from zip code tabulation areas to town names. The USPS probably has something similar.

Upvotes: 1

Related Questions