Как оптимизировать медленный модифицированный запрос обхода дерева предзаказа

Question

У меня есть измененная таблица обхода дерева предзаказа (MPTT) с 82117 записями. Эта таблица mysql содержит некоторые географические данные (Страна, Штат, Регион, Город...)

CREATE TABLE IF NOT EXISTS `geotree` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `geo_id` int(11) DEFAULT NULL,
  `name` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`,`lft`,`rgt`),
  KEY `geo_id` (`geo_id`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=82174 ;

Проблема возникает, когда я пытаюсь получить путь, используя стандартный запрос MPTT, как это:

SELECT p.name 
FROM `geotree` AS node, `geotree` AS p 
WHERE node.lft BETWEEN p.lft AND p.rgt AND node.rgt='$value'
ORDER BY p.lft

Это займет более 2,5 секунд. В чем проблема? Я пропустил какой-либо индекс? Слишком много строк для этого типа структуры данных? Или есть какая-то ошибка в запросе?

ПРИМЕЧАНИЕ. Я заметил, что если я удаляю из запроса "ORDER BY", время запроса уменьшается до 0,05 секунды.

Answer 1

Вы должны по крайней мере преобразовать свой запрос в надлежащее JOIN это должно принести некоторую производительность:

http://sqlfiddle.com/

SELECT p.name 
FROM `geotree` AS node
INNER JOIN `geotree` AS p 
ON node.lft BETWEEN p.lft AND p.rgt 
WHERE node.rgt='$value'

И так как вам нужно node.rgt='$value' в качестве фильтра я бы добавил индекс KEY rgt (rgt)