Jump to content

Spatial index and foreign keys


Andy123

Recommended Posts

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
Share on other sites

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
Share on other sites

(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
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.