MySQL Spatial Data Type Indexing Performance Comparison

Question

Would a MBR function query against an indexed column with geometry type containing points and polygons (GeomCollection type) be slower than a MBR function against an indexed column with a polygon type?

My programmer instincts tell me that it would be slower and I should use the most descriptive spatial type I can.

Edit: After reading Denis's answer, the real question is:

In MySQL "does an indexed column with geometry type containing points and polygons actually contain the latter or are they reduced to a MBR?"

Sorry, but I really do not know. What is "MBR"?

steenhulthin
– steenhulthin

2011-06-09 19:10:20 +00:00
Commented Jun 9, 2011 at 19:10 — steenhulthin
– steenhulthin, Commented Jun 9, 2011 at 19:10
Ah, "Minimum Bounding Rectangle".

steenhulthin
– steenhulthin

2011-06-09 19:19:41 +00:00
Commented Jun 9, 2011 at 19:19 — steenhulthin
– steenhulthin, Commented Jun 9, 2011 at 19:19

Denis de Bernardy · Accepted Answer · 2011-06-09 19:59:49Z

1

I'm risking an answer here, without being sure on the MySQL front...

But in Postgres, geometry types indexed using GIST will usually be indexed as boxes, a.k.a. MBR, irrespective of what whether they're circles, polygones, etc.

So the real question might be better rephrased as: "does an indexed column with geometry type containing points and polygons actually contain the latter or are they reduced to a MBR?"

answered Jun 9, 2011 at 19:59

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

2 Comments

Denis de Bernardy Over a year ago

@Nate: in that case, I'd hazard a yes. (I wouldn't be doing so if MySQL had wizards around that were better at using and optimizing R-tree indexes than the Postgres folks. But the fact is, they don't. Postgres offers Generalized Inverted Search Tree indexes and Generalized Inverted Indexes; both are better. The former, in PG 9.1, allows to leverage an index for k-nearest searches, something that MySQL isn't doing to date. And both work in an ACID compliant environment, whereas MySQL still requires MyISAM for spacial indexes.)

Nate Over a year ago

Okay, I thought that might be the case.

Collectives™ on Stack Overflow

MySQL Spatial Data Type Indexing Performance Comparison

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