mysql spatial data speed

I have a table with (among others) x and y fields of SMALLINT type and pt of POINT type, set to POINT(x,y);

x and y have normal in开发者_StackOverflow中文版decies and pt has a spatial index set.

Profiling typical query

select sql_no_cache
    count(0) from `table_name`
    where (x between -50 and 50)
    and (y between -50 and 50);
-- vs
set @g = GeomFromText('Polygon((-50 -50, 50 -50, 50 50, -50 50, -50 -50))');
select sql_no_cache
    count(0) from `table_name`
    where MBRContains(@g, `pt`);

... shows that query via x and y is 1.5 times faster:

3.45±0.10ms vs 4.61±0.14ms over 10 queries.

x and y would always be INT and only rectangular (even square) areas would be queried. Yes, this is carved in stone ;-)

The main question is:

Have I missed something about indecies or is spatial data an overkill in such case?

• MySQL version is 5.1.37
• DB Engine type is MyISAM (default)
• Current table size is 5k rows, 10-30k planned in production.

I have had some experience with MySQL, but never worked with spatial data types and spatial indecies.

Do you have a combined x & y INDEX on the table? If so then yes, this is extremely fast. I believe Spatial indexes have more broad use. A polygon structure can have many vertices and the rectangle is a single case of a more generic construct.

If a rectangular boundary area is enough for your needs then I would rather suggest you go with the x and y fields solution than adding the complexity of the geospatial extension features.