3

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?

user4951
  • 1,355
  • 5
  • 20
  • 39

2 Answers2

6

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

Remus Rusanu
  • 51,846
  • 4
  • 95
  • 172
4

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.

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
  • What does it mean not supporting indexes on them? Does that mean finding 20 closest store is still going to be expensive? – user4951 Jun 15 '12 at 02: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 07:20