Geek Answers Handbook

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

Our project stores the global database in a tree structure inside the INNODB MySQL db table. Earth is the root, then the country, then the regions of the country and places - the leaves.

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

Now we want to implement a quick geo-search in the database for coordinate data. The 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 the MyISAM table, we must abandon the foreign key, and this will force the children to search too long.

How can we combine a quick search for children in a tree, as well as have a SPACE INDEX in a table?

+7
sql mysql
source share
1 answer

How can we combine a quick search for children in a tree, as well as have a SPACE INDEX in a table?

Create indexes on id and parentId your table manually:

 CREATE INDEX ix_mytable_parentid ON mytable (parentid)

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

BTW , if you have a natural geodatabase hierarchy, what is the point of using parent-child relationships for searches?

You can force queries to use SPATIAL indices:

 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 search and relationships for fine filtering.

+4
source share

More articles:


All Articles