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.