GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Range Optimization

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Range Optimization

    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.

    Comment


    • #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

      Comment

      Working...
      X