Improving a query (peformance)


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?
Who is Participating?
BenefordConnect With a Mentor Commented:
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 -
deviprasadgConnect With a Mentor Commented:
create materialized view with the above query for sharp increase in performance of the select statement.
Tomas Helgi JohannssonConnect With a Mentor Commented:

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. :)

     Tomas Helgi
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.