MySQL与PHP查询性能问题

Question

I have a sql query that runs in like 2 seconds in mysql (Navicat) and it runs through 600,000 locations returning all the table data and calculates the distance between a lat and long that I put in.

The SQL is something like this:

select *, 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - 
radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
FROM places 
GROUP BY places.user_id
HAVING distance > 0
ORDER BY distance LIMIT 0 , 10;

In the above query 37 and -122 are passed into the query as lat and long.

When I run this in Navicat, it's 2 seconds, but it takes minutes in Cakephp or straight php using mysql_query().

Why does this occur and how do I fix it?

I'm running both MySQL and Apache locally on a Win7 4GB box running XAMPP.

Answer 1

This could be related to your table structure. If you have not already, I would highly recommend you create an index on user_id in the places table. This can make a surprising difference in execution time for complex queries.

Also, since it seems you are doing a fair amount of calculation in the query ( for distance ), you may benefit greatly from using the prepared statements feature in the mysqli or PDO extensions - instead of using the older, slower, less-safe, deprecated mysql extension.

This article helps explain the differences: http://php.net/manual/en/mysqli.overview.php

If you are already accustomed to the mysql extension, I would recommend switching to mysqli - their procedural syntax/API is almost identical ( the main difference is that you will need to provide the $connection_handle as the first argument in many functions, instead of it being the optional final argument ).

Navicat may be taking advantage of prepared statements, that may be your difference.