Take the 2-minute tour ×
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.

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?

share|improve this question
add comment

1 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 CACHE syntax and Multiple Key Caches

share|improve this answer
    
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
    
It's not problematic. But there are several ways to take a backup and many of those programs/ways work differently with the different engines. For example, with MyISAM, you can simply stop the mysql service and copy the files. You can't do that with InnoDB tables. –  ypercube Sep 5 '13 at 23:12
add comment

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.