Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

MySQL subquery question

At least I think it's a subquery question....

5:02 pm on Nov 13, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 27, 2008
posts: 65
votes: 0

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.



1:16 am on Nov 19, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 27, 2001
votes: 0

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.


(I'm assuming you don't insert data nearly as often as you extract it.)