MySQL latitude and longitude query and calculate SQL query performance Optimization example tutorial for nearby users in 2KM range

Before very silly very naïve thought is nothing more than a distance, and then compare out on the line, and then when the access to a lot of users, and the database in the latitude information a lot of time, the rapid increase in computational capacity, can make the server completely stupid, or old-timer experience than we rich, gave me a great revelation.

MySQL Performance tuning – distance calculation using a faster algorithm

Recently encountered a problem, through a constant attempt to finally put a sentence originally occupied nearly 1 seconds of the query optimized to 0.01 seconds, efficiency increased 100 times times.

The problem is that there is a MySQL data table that holds the latitude and longitude information for the user's place of residence, and the table structure can be simplified to: id (int), longitude (long), Latitude () long. One function in a business system is to find the remaining number of users closest to a particular user, and through code analysis, it is possible to determine that the original approach is basically this:

The coordinates of the user who needs to be queried

1

$lat=20;

2	$lon=20;//执行查询,算出该用户与所有其他用户的距离,取出最近的10个

3	$sql=‘select * from users_location order by ACOS(SIN((‘.$lat.‘ * 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180 ) +COS((‘.$lat.‘ * 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) *COS((‘.$lon.‘ * 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380 asc limit 10‘;

This SQL execution is very slow, it took nearly 1 seconds to return the results, it should be because the order of the sub-statement with too many mathematical formulas, resulting in the overall speed of operation decreased.

In actual use, it is unlikely that the user will have to calculate the distance from all other users, and then sort the situation, when the number of users reached a level, you can search in a smaller scope, rather than all users to search.

So for this example, I've added 4 where conditions, only for distance calculations for users with longitude and latitude greater than or less than 1 degrees (111 km) of the user, and an index to the longitude and latitude two columns in the data table to optimize the speed at which the where statement executes.

The final SQL statement is as follows

1	$sql=‘select * from users_location where

2	latitude > ‘.$lat.‘-1and

3	latitude < ‘.$lat.‘+1and

4	longitude > ‘.$lon.‘-1and

5	longitude < ‘.$lon.‘+1

6	order by ACOS(SIN((‘.$lat.‘* 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180) +COS((‘.$lat.‘* 3.1415) / 180) * COS((latitude * 3.1415) / 180) *COS((‘.$lon.‘* 3.1415) / 180- (longitude * 3.1415) / 180) ) * 6380asc limit 10‘;

Optimized SQL greatly improves the speed of operation and, in some cases, is even 100 times times higher. This approach to narrowing the scope of SQL queries from a business perspective can also be applied elsewhere.

Original address: http://blog.csdn.net/hustpzb/article/details/7688993

MySQL latitude and longitude query and calculate SQL query performance Optimization example tutorial for nearby users in 2KM range