MySQL&嵌套集：慢JOIN（不使用索引） [英] MySQL & nested set: slow JOIN (not using index)

问题描述

我有两张桌子：

地区：

CREATE TABLE `localities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` varchar(30) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_localities_on_parent_id_and_type` (`parent_id`,`type`),
  KEY `index_localities_on_name` (`name`),
  KEY `index_localities_on_lft_and_rgt` (`lft`,`rgt`)
) ENGINE=InnoDB;

定位：

CREATE TABLE `locatings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `localizable_id` int(11) DEFAULT NULL,
  `localizable_type` varchar(255) DEFAULT NULL,
  `locality_id` int(11) NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_locatings_on_locality_id` (`locality_id`),
  KEY `localizable_and_category_index` (`localizable_type`,`localizable_id`,`category`),
  KEY `index_locatings_on_category` (`category`)
) ENGINE=InnoDB;

localities表实现为嵌套集。

localities table is implemented as a nested set.

现在，当用户属于某个地区（通过某些定位）时，他也属于其所有祖先（更高级别的地方）。我需要一个查询，将所有用户所属的所有地点选择到视图中。

Now, when user belongs to some locality (through some locating) he also belongs to all its ancestors (higher level localities). I need a query that will select all the localities that all the users belong to into a view.

这是我的尝试：

select distinct lca.*, lt.localizable_type, lt.localizable_id 
from locatings lt
join localities lc on lc.id = lt.locality_id
left join localities lca on (lca.lft <= lc.lft and lca.rgt >= lc.rgt)

这里的问题是需要花费太多时间来执行。

The problem here is that it takes way too much time to execute.

我咨询了EXPLAIN：

I consulted EXPLAIN:

+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
| id | select_type | table | type   | possible_keys                   | key     | key_len | ref                              | rows  | filtered | Extra           |
+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
|  1 | SIMPLE      | lt    | ALL    | index_locatings_on_locality_id  | NULL    | NULL    | NULL                             |  4926 |   100.00 | Using temporary |
|  1 | SIMPLE      | lc    | eq_ref | PRIMARY                         | PRIMARY | 4       | bzzik_development.lt.locality_id |     1 |   100.00 |                 |
|  1 | SIMPLE      | lca   | ALL    | index_localities_on_lft_and_rgt | NULL    | NULL    | NULL                             | 11439 |   100.00 |                 |
+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

最后一次加入显然不会像我期望的那样使用lft，rgt索引。我很绝望。

The last join obviously doesn’t use lft, rgt index as I expect it to. I’m desperate.

更新：
在@cairnz建议添加条件后，查询仍然需要花费太多时间来处理。

UPDATE: After adding a condition as @cairnz suggested, the query takes still too much time to process.

更新2：列名而不是星号

UPDATE 2: Column names instead of the asterisk

更新的查询：

SELECT DISTINCT lca.id, lt.`localizable_id`, lt.`localizable_type` 
FROM locatings lt FORCE INDEX(index_locatings_on_category)
JOIN localities lc
    ON lc.id = lt.locality_id
INNER JOIN localities lca
    ON lca.lft <= lc.lft AND lca.rgt >= lc.rgt
WHERE lt.`category` != "Unknown";

更新了EXAPLAIN：

Updated EXAPLAIN:

+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
| id | select_type | table | type   | possible_keys                           | key                         | key_len | ref                             | rows  | filtered | Extra                                           |
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
|  1 | SIMPLE      | lt    | range  | index_locatings_on_category             | index_locatings_on_category | 153     | NULL                            |  2545 |   100.00 | Using where; Using temporary                    |
|  1 | SIMPLE      | lc    | eq_ref | PRIMARY,index_localities_on_lft_and_rgt | PRIMARY                     | 4       | bzzik_production.lt.locality_id |     1 |   100.00 |                                                 |
|  1 | SIMPLE      | lca   | ALL    | index_localities_on_lft_and_rgt         | NULL                        | NULL    | NULL                            | 11570 |   100.00 | Range checked for each record (index map: 0x10) |
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+

任何帮助表示赞赏。

推荐答案

尝试使用强制索引进行试验 - http://dev.mysql.com/doc/refman/5.1/en/index-hints.html ，也许这只是优化问题。

try to experiment with forcing index - http://dev.mysql.com/doc/refman/5.1/en/index-hints.html, maybe it's just optimizer issue.

这篇关于MySQL&嵌套集：慢JOIN（不使用索引）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！