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?
Best Answer
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 CACHEsyntax and Multiple Key Caches