Is mysql using my index or not, and can the performance of geokit be improved?

Question

I'm using geokit (acts_as_mappable) in a rails application, and the performance of radial or bounds searches degrades considerably when there is a large number of models (I've tried with 1-2million but the problem no doubt kicks in earlier than this). Geokit does all its calculations based on lat and lng columns in the table (latitude and longitude). To improve performance geokit will typically add a bounding box 'where' clause, with the intent being to use a combined index on latitude and longitude to improve performance. However it is still incredibly slow with large numbers of models, and it seems to me that the bounding box clause should help a lot more than it does. So my question is, is there a way to make mysql make better use of the combined lat/lng index or otherwise improve the performance of geokit sql queries? Or, can the combined index for lat/lng be made more helpful? edit: I've got this working with rails now and written the solution up in more detail here More Backgrou…

Accepted Answer

Plain `B-Tree` indexes are not too good for the queries like this.

For your query, the `range` access method is used on the following condition:

```
places.lat > 51.3373601471464 AND places.lat < 51.6264998528536
```

, this doesn't even take `lon` into account.

If you want to use spatial abilities, you should keep your places as `Points`, create a `SPATIAL` index of them and use `MBRContains` to filter the bounding box:

```
ALTER TABLE places ADD place_point GEOMETRY

CREATE SPATIAL INDEX sx_places_points ON places (place_point)

UPDATE  places
SET     place_point = Point(lat, lon)

SELECT  *
FROM    places
WHERE   MBRContains(LineString(Point(51.3373, -1.1330), Point(51.6264, -0.6687)), place_point)
        AND -- do the fine filtering here
```

Update:

```
CREATE TABLE t_spatial (id INT NOT NULL, lat FLOAT NOT NULL, lon FLOAT NOT NULL, coord GEOMETRY) ENGINE=MyISAM;

INSERT
INTO    t_spatial (id, lat, lon)
VALUES  (1, 52.2532, 20.9778);

UPDATE  t_spatial
SET     coord = Point(lat, lon);
```

This works for me in `5.1.35`.

Is mysql using my index or not, and can the performance of geokit be improved?

Solution

Problem