Andy123 Posted January 28, 2013 Share Posted January 28, 2013 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. Link to comment https://forums.phpfreaks.com/topic/273715-spatial-index-and-foreign-keys/ Share on other sites More sharing options...
DavidAM Posted January 28, 2013 Share Posted January 28, 2013 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. Link to comment https://forums.phpfreaks.com/topic/273715-spatial-index-and-foreign-keys/#findComment-1408667 Share on other sites More sharing options...
Andy123 Posted January 28, 2013 Author Share Posted January 28, 2013 (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 Link to comment https://forums.phpfreaks.com/topic/273715-spatial-index-and-foreign-keys/#findComment-1408730 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.