Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I am very curious.

I have business tables. Now I think I will have to create a separate table, location table. That separate table should be myisam.

But why would I do so?

Why can't innodb store points?

share|improve this question

2 Answers

up vote 5 down vote accepted

Because R-Trees are not B-Trees:

For MyISAM tables, SPATIAL INDEX creates an R-tree index. For storage engines that support nonspatial indexing of spatial columns, the engine creates a B-tree index. A B-tree index on spatial values will be useful for exact-value lookups, but not for range scans.

Adding a completely different storage structure for InnoDb is significant effort (much more than for MyISAM due to locking and recovery).

share|improve this answer
up vote 4 down vote

Actually, it can. But it doesn't support spatial indices on them, yet. See the official Mysql docs Limits on InnoDB Tables:

InnoDB tables do not support spatial data types before MySQL 5.0.16. As of 5.0.16, InnoDB supports spatial data types, but not indexes on them.

share|improve this answer
What does it mean not supporting indexes on them? Does that mean finding 20 closest store is still going to be expensive? – Jim Thio Jun 15 '12 at 2:52
Yes, finding closest store effectively requires spatial index (R-tree). Read the links from Remus answer. So, yes, it will be expensive. – ypercube Jun 15 '12 at 7:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.