Optimize MySQL Code

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

WanamakerStudios wrote:If I store the distances in an array, how can I sort the final results by distance then?
Have you searched these forums for 'Zip Code Search' (all terms)? This has been done quite a few times before (I think I have posted several solutions to this before as well).

You shouldn't have to make temp tables. You can use the array sorting functions of PHP to handle your distance sorts. You should be able to execute the proximity matching with one query and get all the records you want in another. You can combine the two, but you will be looking at a fairly slow query (maybe 0.3 to 0.5 seconds). 9 seconds suggests your indexes are bad.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Everah wrote:
WanamakerStudios wrote:If I store the distances in an array, how can I sort the final results by distance then?
Have you searched these forums for 'Zip Code Search' (all terms)? This has been done quite a few times before (I think I have posted several solutions to this before as well).

You shouldn't have to make temp tables. You can use the array sorting functions of PHP to handle your distance sorts. You should be able to execute the proximity matching with one query and get all the records you want in another. You can combine the two, but you will be looking at a fairly slow query (maybe 0.3 to 0.5 seconds). 9 seconds suggests your indexes are bad.
I believe the main issue was not the zip code part as that was pretty much tackled in his SQL already, but he was having trouble wrapping his head around joining all the tables together that he needs. In the follow up posts it appears he has used my suggestions to get this into one query and eliminated the temp tables. (perhaps you just skimmed those posts :wink: ) Now he is just having minor troubles getting unique records.
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

And my lack of replies may have added to a bit of the problem as well :wink:

I have been able to combine everything into one query and added a DISTINCT request so that I don't return multiple results of the same vehicle. So I would say that this one has been solved! Thank you for all of the help!
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

But is it fast?
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Yes! Very much so! It turned out that the optimized coding needed to be coupled with a slightly different indexing method. I have another post (viewtopic.php?t=60968) where I need to know how to take multiple JOINs of the same table and combine them against multiple fields in another table. Having coded the way I have it now slows it down slightly ... but when I look at the true numbers ... it isn't too bad.
Post Reply