2 MySQL queries to perform: which way is faster?

[djmatt]

I want to do a search of businesses in a 150 mile radius from a provided zip code. I have a zip code database which I can loop through and have it give me each zip code that matches that query. Then, I want to search another table to see if there my database lists a business in that zip code (for example: say 90210 matches the query, then, look to find any businesses that listed 90210 as their zip).

Would it be faster to run the first query and get all the zip codes that match using an array and then run the query looking for the business zip codes or find one zip code and then search for any businesses, then find the next code, and find their matches, etc.? I can tell based on page loading that it is taking a while, so I would like to make it as efficient as possible.

Thanks.

[jseedev]

Maybe try loading the zip code array into a session variable on a preliminary script? Then use the said array to build your SQL query on the next page. This could also be useful for caching the zip codes across multiple pages without having to re-query for zip codes each time. My only concern with this method would be the size of the array.

[jseedev]

Assuming you're using PHP? I would load the zip code array into a session variable (on a preliminary page) to keep from having to query for them each time. Then use the said array to assemble your queries on the next page.

[djmatt]

Yes, I am using php.

That is a good idea. However, the typical visitor to this site will only search the database once. So I still am wondering if it is faster to go through an entire loop of queries and then go back and do another loop of queries using the first results or do each second query as soon as I get the first result.

[Adam]

I guess I'm a little late to the party here, but..

It sounds like you should be running one query to snag all of the surrounding zip codes, then a second query to grab all of the businesses in said zip codes, both of which run on indexed columns and should complete very quickly.

Have you tried running an EXPLAIN on the queries you're using to see why it's taking so long to pull your results?

[alemcherry]

Find all the zip codes, put them into a string separated by commas and run one query on second table with "where zip_code in ('123,'456') " format.

Also since these queries can take time, use a PHP timer to calculate the exact time taken for queries. Find the time before and after queries and echo the difference. A programmer can't measure the efficiency by guessing the page load time.

[SimonSays]

Why perform two queries, when your can do it with one, joining the two table with only the columns you need? You could even make it a view. Am I missing something?