How to use foreign keys and a spatial index inside a MySQL table?

Question

Hey!

Our project keeps world data base in tree-structure inside INNODB table of MySQL db. Earth is the root, then countries, then country regions and the locations are the leaves.

A foreign-key is used to provide a fast access to children (for example cities in a region).

Now we want to implement a fast geo-search in the data-base for given coordinates. An obvious solution is to use SPATIAL INDEX, which is a feature of MyISAM tables. But MyISAM tables do not support foreign keys. And INNODB tables do not support SPATIAL INDEX.

So if we use MyISAM table we have to abandon foreign key and that would make children search way too long.

How can we combine fast children search in tree and also have a SPATIAL INDEX in a table?

Answer 1

How can we combine fast children search in tree and also have a SPATIAL INDEX in a table?

Create the indexes on id and parentId of your table manually:

CREATE INDEX ix_mytable_parentid ON mytable (parentid)

Note that since id is most probably a PRIMARY KEY, no explicit index is required on it (one will be created implicitly).

BTW, if you are having the natural geo-based hierarchy, what's the point of using parent-child relationships for searching?

You can make the queries to use the SPATIAL indexes:

SELECT  *
FROM    mytable m1
JOIN    mytable m2
ON      MBRContains (m2.area, m1.area)
        AND m2.parentId = m1.id
WHERE   m1.name = 'London'

which will use the spatial index for searching and the relationship for fine filtering.