Spatial index and foreign keys

[Andy123]

Hey guys,

 

 

I am looking into using MySQL's Spatial Extensions. However, I noticed that spatial indexes are not available for InnoDB tables which I am using to implement foreign key constraints. Instead, it is available on MyISAM tables, which do not support foreign keys. I have a table with user profiles and have a foreign key to a "city" table on which I would like to have a spatial index. To have this index, I need to use MyISAM, but then I have to throw my foreign key out the window.

 

I can get the same performance if I add an index on the "foreign" key on the profile table; I can then simply join with the primary key of the "city" table just as before. The only difference is that I do not have a foreign key. This means that I lose the relationship constraint and managing the integrity of my key is then up to me. If I am not careful, I could add a wrong value which would not match anything in the "city" table and therefore a join cannot be done. Updating the "city" table will also be more complicated because then I have to update all rows in my referencing table myself. However, this is not a big concern.

 

So, to sum up, to use the spatial index, I need to use MyISAM and lose my foreign key. Is the optimal solution to index my join columns properly and handle the correctness of my "foreign key" myself? Does anyone have any other ideas (or thumbs up to my approach)?

 

Thanks.

[DavidAM]

MySQL supports spatial extensions to enable the generation, storage, and analysis of geographic features. Before MySQL 5.0.16, these features are available for MyISAM tables only. As of MySQL 5.0.16, InnoDB, NDB, BDB, and ARCHIVE also support spatial features.

(emphasis added)

 

from the mySql 5.0 Manual

 

Considering mySql is at 5.6, using 5.0 is not a real good idea.

[Andy123]

(emphasis added)

 

from the mySql 5.0 Manual

 

Considering mySql is at 5.6, using 5.0 is not a real good idea.

 

It is true that InnoDB supports spacial types (I am indeed using 5.6). I have successfully created a table with a spacial column and inserted data into it. Unfortunately, it still does not support spatial indexes.

 

For spatial columns, MyISAM supports both SPATIAL and non-SPATIAL indexes. Other storage engines support non-SPATIAL indexes, as described in Section 13.1.11, “CREATE INDEX Syntax”.

 

InnoDB tables support spatial data types, but not indexes on them. http://dev.mysql.com...strictions.html