Jump to content


Photo

Spatial index and foreign keys

spatial extensions myisam innodb foreign key

  • Please log in to reply
2 replies to this topic

#1 Andy123

Andy123

    Advanced Member

  • Members
  • PipPipPip
  • 134 posts
  • LocationDenmark

Posted 27 January 2013 - 09:07 PM

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.
Blogging about PHP and other programming related subjects at CodingExplained.com.

#2 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,949 posts
  • LocationSpring, TX USA

Posted 27 January 2013 - 11:08 PM

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.
-- I haven't lost my mind, it's backed up on tape ... somewhere!

#3 Andy123

Andy123

    Advanced Member

  • Members
  • PipPipPip
  • 134 posts
  • LocationDenmark

Posted 28 January 2013 - 07:28 AM

(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


Blogging about PHP and other programming related subjects at CodingExplained.com.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com