homepage Welcome to WebmasterWorld Guest from 54.211.157.103
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:3785755
 5:02 pm on Nov 13, 2008 (gmt 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.

Thoughts?

Joe

 

physics




msg:3789325
 1:16 am on Nov 19, 2008 (gmt 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.

[dev.mysql.com...]

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved