Speed up mysql query for distance (latitude,longitude)

Question

I have a table with more than 8 millions record (geonames). Inside this table there are 19 columns, 2 of them are latitude and longitude.

I wish to find nearest place from latitude and longitude values and I do this query:

SELECT * , ( 6371 * ACOS( COS( RADIANS( 40.8333333 ) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS( 14.25 ) ) + SIN( RADIANS( 40.8333333 ) ) * SIN( RADIANS( latitude ) ) ) ) AS distance
FROM geoname
WHERE fclass =  'P'
HAVING distance <25
ORDER BY distance
LIMIT 0 , 20

I set a btree index whit fclass, latitude and longitude.

The issue is that the query take 5.6027 seconds. too much. Is there a way to optimize it? I'm doing something wrong?

Thanks

Answer 1

To the best of my knowledge, regular relational databases aren't meant to provide facilities for spatial approximate queries.

In your place, I'd either move my data to a spatial database, or insert my data into a metric tree (a kd-tree may be the best choice here) and issue the query against the tree.

Answer 2

You can try your luck with spatial indices. That'll come at the cost of using the MyISAM storage engine, though,