mysql - 优化基于分层数据的类别表

Question

我有一个基于本教程的类别表:http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

表的结构非常简单:

id, lft, rgt

这是查询:

SELECT c . *, (COUNT(DISTINCT(parent.id))) AS level
FROM categories AS parent, categories AS c
WHERE c.lft
BETWEEN parent.lft
AND parent.rgt
GROUP BY c.id
ORDER BY c.lft

如您所见，同一张表被使用了两次。有一个 between 子句，它有 group by 和 order by。

我完全不确定应该如何优化它。

当对此使用 EXPLAIN 时，我得到:

Using index; Using temporary; Using filesort

Answer 1

我建议您尝试使用 closure table 的方法- 更简单的想法和更易读的查询。您将需要一个 INSERT/UPDATE 触发器以及包含 parent 与其子女/孙子女/孙子女之间所有可能关系的闭包表......还包括节点本身(DEPTH = 0)

+-------------+----------+-------+
| ancestor_id | child_id | depth |
+-------------+----------+-------+