# 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?
###### Who is Participating?

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 - http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html.
0

Commented:
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

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