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.
Have you tried the MySQL query cache? That might speed up step 3.
The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.