Range Optimization

MySQL & MariaDB Other MySQL® Questions
1

Hi,

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.

Thanks,
Oliver

2

Similar to this.

3

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