I have a database which is in InnoDB, because I wanted consistency (cascading delete).

Now, I want to use spatial indexes for selecting rows ordered by geometric distance.

However, spatial indexes require MyISAM. What is the best thing to do?

The best I could think of is to put the location in a separate MyISAM table and join the InnoDB table with it? (Note that I have removed the spatial magic from the statement to simplify things.)

select * from inno join isam using (id) order by isam.distance

Other solutions?

  • 1
    See my answer for an update. – Evan Carroll Jun 22 '18 at 5:09

Yes, that's the best I think. You have the benefit of both InnoDB (referential integrity) and MyISAM (spatial indexes.)

The only technical difficulty is to ensure that the MyISAM table is synchronized with the InnoDB one. This can be done either with triggers or by stored procedures (so every operation on the InnoDB table is done through a procedure.)

Another "benefit" is that since the coordinate values are duplicated at both tables, you can skip backing up the MyISAM table (or backing up separately) and have a procedure to recreate it after a database restore. You probably need a procedure to check periodically that that the two tables are in sync, too.

To speed up queries that need the spatial index - and if the table is not huge - you can make sure that the index is loaded in memory in advance, even in a separate cache. See LOAD INDEX INTO CACHE syntax and Multiple Key Caches

  • Why would you suggest duplicating the location in both tables, rather than keeping it just in the MyISAM table where it is required? – forthrin Sep 5 '13 at 14:57
  • Because myisam tables can crash and you can lose data. – ypercubeᵀᴹ Sep 5 '13 at 15:03
  • And backing up only 1 type of tables is easier than dealing with 2 types, both myisam and innodb. – ypercubeᵀᴹ Sep 5 '13 at 15:09
  • How does is backing up two database types problematic? – forthrin Sep 5 '13 at 19:33
  • 1
    Note that MySQL 5.7 will have support for spatial indexes in INNODB tables, as evidenced by the recent lab releases. – Carlos P Oct 8 '14 at 13:58

MySQL 5.7+

In MySQL 5.7, InnoDB got Spatial Indexes, from What Is New in MySQL 5.7

InnoDB now supports MySQL-supported spatial data types. Prior to this release, InnoDB would store spatial data as binary BLOB data. BLOB remains the underlying data type but spatial data types are now mapped to a new InnoDB internal data type, DATA_GEOMETRY.

I believe this was part of the rewrite/refactor that brought in Boost

See also,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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