Как использовать внешние ключи и пространственный индекс в таблице MySQL?
Наш проект сохраняет мировую базу данных в древовидной структуре в таблице INNODB дб MySQL. Земля является корнем, затем страны, затем регионы страны и местоположения являются листами.
Внешний ключ используется, чтобы предоставить быстрый доступ детям (например, города в регионе).
Теперь мы хотим реализовать быстрый геопоиск в базе данных для данных координат. Очевидное решение состоит в том, чтобы использовать ПРОСТРАНСТВЕННЫЙ ИНДЕКС, который является функцией таблиц MyISAM. Но таблицы MyISAM не поддерживают внешние ключи. И таблицы INNODB не поддерживают ПРОСТРАНСТВЕННЫЙ ИНДЕКС.
Таким образом, если бы мы используем таблицу MyISAM, мы должны отказаться от внешнего ключа, и это заставило бы детей искать слишком долго.
Как мы можем объединиться, быстрые дети ищут в дереве и также имеют ПРОСТРАНСТВЕННЫЙ ИНДЕКС в таблице?
1 ответ
Как можно совместить быстрый поиск детей на дереве, а также иметь СПАТИАЛЬНЫЙ ИНДЕКС в таблице?
Создайте индексы на id и parentId вашей таблицы вручную:
CREATE INDEX ix_mytable_parentid ON mytable (parentid)
Обратите внимание, что поскольку id скорее всего является PRIMARY KEY, то никакого явного индекса не требуется на it (один из них будет создан неявно).
BTW, если у вас естественная иерархия, основанная на геологии, то какой смысл использовать отношения родитель-ребенок для поиска?
Вы можете заставить запросы использовать SPATIAL индексы:
SELECT *
FROM mytable m1
JOIN mytable m2
ON MBRContains (m2.area, m1.area)
AND m2.parentId = m1.id
WHERE m1.name = 'London'
которые будут использовать пространственный индекс для поиска и отношения для тонкой фильтрации.