LBs queries data from a database in the range of a longitude and latitude of 2KM-Performance Optimization for large data volumes

In the past, I was naive to think that it was nothing more than calculating the distance one by one, and then comparing them out. When there were a lot of users accessing the database and there was a lot of latitude and longitude information in the database, the rapid increase in computing workload can make the server completely dumb, or the experience of the old generation is richer than ours, which gives me a lot of inspiration. SQL statement query latitude and longitude range

specify a latitude and longitude, and specify a range value (in kilometers ), find the data in the range around the latitude and longitude.
longitude: 113.914619
latitude: 22.50128
range: 2 km
longpolling is the longitude field of the data table
latitude is the latitude field of the data table
SQL passed the test in MySQL. Other databases may need to modify
the SQL statement is as follows:
select * from location where SQRT (113.914619-longpolling) * Pi () * 12656 * Cos (22.50128 + latitude)/2) * Pi ()/180)/180) * (113.914619-longpolling) * Pi () * 12656 * Cos (22.50128 + latitude)/2) * Pi () /180)/180) + (22.50128-latitude) * Pi () * 12656/180) * (22.50128-latitude) * Pi () * 12656/180 ))) <2

MySQL performance tuning-distance using faster Algorithms

Recently, I encountered a problem. Through continuous attempts, I finally optimized the query of a sentence that originally occupied nearly 1 second to 0.01 seconds, improving the efficiency by 100 times.

The problem is that there is a MySQL DATA table that stores the longitude and latitude information of the user's place of residence. The table structure can be simplified to: ID (INT), longpolling (long), latitude () Long. one function in the Business System is to find the remaining users closest to a user.CodeAnalysis, we can confirm that the original practice is basically like this:

// Coordinates of the user to be queried

$ Lat =  20 ; $ Lon = 20 ; // Run the query to calculate the distance between the user and all other users, and retrieve the latest 10 $ SQL statements. =  '  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-(longpolling * 3.1415)/180) * 6380 ASC limit 10 ' ;

However, the execution speed of this SQL statement is very slow. It takes nearly one second to return the result. It should be because the substatements in order use too many mathematical formulas, the overall operation speed is reduced.

In actual use, it is unlikely that the distance between the user and all other users needs to be calculated and then sorted. When the number of users reaches a certain level, you can search in a small range instead of all users.

Therefore, in this example, I added four where conditions to calculate the distance between users whose longitude and latitude are greater than or less than the user's 1 degree (111 km) range, at the same time, the longitude and latitude columns in the data table are indexed to optimize the speed at which the where statement is executed.

The final SQL statement is as follows:

$ SQL =  '  Select * From users_location where latitude>  ' . $ Lat. '  -1 and latitude < ' . $ Lat. '  + 1 and longpolling>  ' . $ Lon. '  -1 and longpolling <  ' . $ Lon. ' + 1 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-(longpolling * 3.1415)/180) * 6380 ASC limit 10  ' ;

The optimized SQL statements greatly increase the running speed and, in some cases, even increase by 100 times. This method can also be applied to other places to narrow the SQL query scope from the business perspective.