mysql spatial data speed

Question

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 indecies 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.

georgepsarakis · Accepted Answer · 2011-09-04 10:20:40Z

1

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.

answered Sep 4, 2011 at 10:20

georgepsarakis

1,9574 gold badges21 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kirilloid Over a year ago

Hmm, didn't saw any combined indecies in that version of MySQL

georgepsarakis Over a year ago

By combined index I mean an INDEX on two or more fields. I believe any version of MySQL does that. You can add an INDEX on both x & y columns: ALTER TABLE table_name ADD INDEX(x,y); Keep in mind that queries using condition only on the x field will benefit from this index, SELECT * FROM table_name WHERE x BETWEEN 100 AND 200; while queries with a condition only using the y field will NOT use this index.

Collectives™ on Stack Overflow

mysql spatial data speed

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related