Range Optimization


I have a MyISAM table with about 2400000 geonames. Ther are 2 decimal columns for latitude and longitude.

I use the following query to select all rows within a square area, in this case 10 x 10 km:

SELECT * FROM geonames WHERE latitude < 46.099099099099 AND latitude > 45.900900900901 AND longitude < 2.6422473920993 AND longitude > 2.3577526079007

The problem is that MySQL is able to use index only on one of the lat/lng columns, so the query does not run as fast as I would need.
I have tried all possible index combinations: (latitude), (longitude) and (latitude, longitude) but nothing seems to work.

Any help will be greatly appreciated.


Similar to this.

Have you looked into the MySQL Spatial Extensions? I’m not too familiar with them myself, but they’re designed to make queries like yours (GIS) fast.

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.ht ml