• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Improving a query (peformance)

Hello,

I have the following query (great circle computation):

SELECT id, city_name, ( 6371 * acos( cos( radians(37.102528) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(25.376093) ) + sin( radians(37.102528) ) * sin( radians( lat ) ) ) ) AS distance
FROM city_names HAVING distance < 25 OR distance IS NULL ORDER BY distance LIMIT 0 , 200;

My table has about 15M records and this query takes from 13 to 22 sec to execute. I have an index on lat and long fields (both fields on the same index).

Are there any ways to improve the speed of this query?
0
infodigger
Asked:
infodigger
3 Solutions
 
deviprasadgCommented:
create materialized view with the above query for sharp increase in performance of the select statement.

http://stackoverflow.com/questions/2534506/how-to-implement-materialized-view-with-mysql
0
 
BenefordCommented:
Do you have a small number of locations from which the distance is calculated?
If so, an additional column in the table for each destination would allow you to pre-calculate (in 22s) and then run the calculation.

If there are multiple centre points, you should see an improvement by:
1. Precalculate radians ( fixed-value ) - just multiply by pi/180.
2. Precalculate sin(radians(lat)) - (same for cos and lng) and store the values in the table
3. Move the Earth-size multiplier (6371) into the having clause: HAVING distance < 25/6371

Note: if you're looking at small distances, you may want to use the Haversine formula, but that may not be quicker.

Better (but I have no experience of it) might be the MySQL 5 Spacial Extensions - http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html.
0
 
Tomas Helgi JohannssonCommented:
Hi!

For this query to have optimal performance you should create an index like this
CREATE INDEX city_dist_ix ON TABLE city_names (lat,long, city_name,id)  USING BTREE;

This will make the query index only query and hopefully a little bit faster. :)

Regards,
     Tomas Helgi
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now