Something must be wrong with your mysql installation or the .ini settings.
Just tested a geospatial index on my old mac (10.6.8 / MySQL 5.2).
That configuration is similar to yours and I tested the big geodata dump
(9 million records).
I did this query:
SET @radius = 30;
SET @center = GeomFromText('POINT(51.51359 7.465425)');
SET @r = @radius/69.1;
SET @bbox = CONCAT('POLYGON((',
X(@center) - @r, ' ', Y(@center) - @r, ',',
X(@center) + @r, ' ', Y(@center) - @r, ',',
X(@center) + @r, ' ', Y(@center) + @r, ',',
X(@center) - @r, ' ', Y(@center) + @r, ',',
X(@center) - @r, ' ', Y(@center) - @r, '))'
);
SELECT geonameid, SQRT(POW( ABS( X(point) - X(@center)), 2) + POW( ABS(Y(point) - Y(@center)), 2 ))*69.1
AS distance
FROM TABLENAME AS root
WHERE Intersects( point, GeomFromText(@bbox) )
AND SQRT(POW( ABS( X(point) - X(@center)), 2) + POW( ABS(Y(point) - Y(@center)), 2 )) < @r
ORDER BY distance;
It took just 0.0336 sec.
I do use the above query e.g. for comparisons between tables where the table where just the lat/lng values for @center come from has a plain INDEX from city_latitude/city_longitude and the 9-12 Mio. table from geonames.org has a geospatial index.
And I just wanted to add that when anybody inserts the big data in a table it might be more performant to add the index after INSERT.
If not it will take longer for each row you add ...
[but that's not important]
Spatial fragmentation can make a random spatial distribution of data perform poorly, no matter how you tune the spatial index. Try duplicating the table by exporting the features by county id or some other attribute that clusters the data (or if you have to, by a systematic search grid that partitions the data by at least in ten tiles in each dimension), then rebuilding the index. If the data had been fragmented, you might see a significant improvement in spatial search efficiency.
Note: Optimizing for spatial search could have an impact on non-spatial queries; if your queries are compounnd in nature (e.g., time window and spatial envelope), then reorgainizing to split the difference (spatially, by month) can be a compromise, as can keeping two copies of the table, with differing clustered indexes, and queries directed to the table which is most friendly to the constraints.
Best Answer
For displaying purposes it is always good to use a spatial index. It will improve speed of both rendering and spatial queries. However, if you plan to update large quantities of objects, it might be wise to remove the spatial index during the update. Otherwise the update process will become significantly slower, because with every update the spatial index needs to be updated as well. After updating you can add a spatial index again.