So - I have a 10-table database containing a variety of info about ~400 schools and their associated campuses and degree programs. I am trying to rewrite a ZIP code proximity search function on my site to work with this database. Right now the ZIP search goes off a denormalized table and runs pretty fast.
The rough logic of the function is:
1. user enters ZIP
2. function queries a separate table (of all US zip code, lat/lon, etc.) to determine which ZIP codes are within a predetermined distance of the ZIP code the user entered
3. for each one of those ZIP codes, the function queries the database to get all rows that match that zip code
4. when it's done cycling through all the ZIP codes, the results are formatted for display
I think what's slowing it down is the amount of joining it has to do now in step 3. I don't use all 10 tables, maybe 6 of them, but still.
Thoughts?
Joe
