Индексы вложенного набора и производительность

У меня проблемы с пониманием того, какие индексы использовать в модели вложенного набора. Запрос:

SELECT `node`.`id`,(COUNT(parent.id) - 1) AS `depth`,`name` FROM `categories` AS `parent` 
INNER JOIN `categories` AS `node` ON (`node`.`lft` BETWEEN parent.lft AND parent.rgt)
INNER JOIN `filebank_categories` ON (`node`.`id` = `filebank_categories`.`category_id` AND `filebank_categories`.`filebank_id` = 136)
INNER JOIN `categories_names` ON (`categories_names`.`category_id` = `node`.`id` AND `categories_names`.`language_id` = 1) 
WHERE `node`.`system_id` = parent.system_id 
GROUP BY node.id 
ORDER BY `node`.`lft` ASC

Этот запрос занимает ~ 350 мс с ~ 5000 строк в categories. EXPLAIN дает следующее:

1   SIMPLE  filebank_categories     ref     fk_filebank_categories_categories1,filebank_id          filebank_id 5   const                               474 Using where; Using temporary; Using filesort
1   SIMPLE  node                    eq_ref  PRIMARY,lft,category,cat,lft,rgt,system,id,lft,system   PRIMARY     4   filebank_categories.category_id     1    
1   SIMPLE  parent                  ref     lft,category,system                                     system      5   node.system_id                      50  Using where
1   SIMPLE  categories_names        eq_ref  PRIMARY,fk_categories_names_categories1                 PRIMARY     8   node.id,const                       1   Using where

Структура таблицы:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `system_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lft,category` (`lft`,`id`),
  KEY `cat,lft,rgt` (`id`,`lft`,`rgt`),
  KEY `system` (`system_id`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`system_id`) REFERENCES `systems` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11519 DEFAULT CHARSET=utf8;

Есть идеи, как это улучшить? Это вообще возможно? У меня нет большого опыта в оптимизации баз данных, поэтому я не могу понять, какие индексы здесь использовать (и почему).

Спасибо.


mysql query-optimization nested-sets
person Per H    schedule 13.12.2010    source источник

Ответы (1)


arrow_upward
5
arrow_downward

Вы можете попробовать переместить WHERE clause, чтобы присоединиться, например

SELECT ...
INNER JOIN `categories` AS `node` ON 
(
  node.system_id=parent.system_id AND 
  node.lft BETWEEN parent.lft AND parent.rgt
)

И уточните индекс как:

CREATE TABLE `categories` (
  ...
  KEY `system_id,lft,rgt` (`system_id`,`lft`,`rgt`),
  ...
);
person ajreal    schedule 13.12.2010
comment
+1 для индекса, старый KEY cat,lft,rgt (_2 _, _ 3 _, _ 4_) был бесполезен для сравнения; Также Per, возможно, вы захотите прекратить использовать запятые в именах ваших ключей - это делает чтение возможных ключей в плане запроса интересным упражнением. - person Unreason; 13.12.2010
comment
Спасибо! Это помогает, теперь запрос выполняется примерно за 100 мс. Но я все еще использую временный; Использование filesort для filebank_categories, что, как я думал, замедляет работу больше всего? - person Per H; 14.12.2010
comment
filesort в порядке (имя filesort не очень точное), использование временного вызвано группировкой - person ajreal; 14.12.2010