MySQL和嵌套集：慢JOIN（不使用索引）
我有两个表格：
地点：
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;
locatings：
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;
地方表被实施为嵌套集合。
现在，当用户属于某个地区时（通过某种定位），他也属于其所有祖先（更高级别的地区）。 我需要一个查询来选择所有用户所属的所有地方。
这是我的尝试：
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)
这里的问题是执行需要太多的时间。
我咨询了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索引。 我很绝望。
更新：在@cairnz建议添加条件之后，查询仍需要太多时间来处理。
更新2：列名而不是星号
更新的查询：
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：
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
| 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) |
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
任何帮助赞赏。
啊，它发生在我身上。
由于您要求表中的所有内容，因此mysql决定使用全表扫描，因为它认为它更高效。
为了获得一些关键用法，添加一些过滤器来限制查找所有表中的每一行。
更新答案：
你的第二个查询没有意义。 你将会加入到lca中，但是你有一个过滤器，这会抵消左连接本身。 另外，您还需要查询查询的最后一步中的数据，这意味着您必须查看lt，lc和lca中的所有内容才能找到您的数据。 此外，您还没有索引位置上最左侧的“类型”列，因此您仍然需要全表扫描才能找到您的数据。
如果你有一些样本数据和你试图实现的例子，它可能会更容易帮助。
尝试尝试强制索引 - http://dev.mysql.com/doc/refman/5.1/en/index-hints.html，也许这只是优化器问题。
看起来你想要单一结果的父母。
根据在SQL中定义嵌套集的人，Joe Celko在http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html“这个模型是一种显示零件爆炸的自然方式，因为最终的装配是由物理嵌套组件组成，分解成不同的部分。“
换句话说，嵌套集用于在单个集合中有效地筛选儿童到任意数量的独立级别。 你有两张桌子，但是我看不到集合“定位”的属性不能被解除标准化为“地点”？
如果地方表有一个几何列，我能不能从一个“定位”中找到一个地点，然后使用一个过滤器在一个表上选择：parent.lft <= row.left AND parent.rgt> = row.rgt ？
更新
在这个回答https://stackoverflow.com/a/1743952/3018894，有一个来自http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/的例子，其中以下示例将所有祖先的深度设置为100000：
SELECT hp.id, hp.parent, hp.lft, hp.rgt, hp.data
FROM (
SELECT @r AS _id,
@level := @level + 1 AS level,
(
SELECT @r := NULLIF(parent, 0)
FROM t_hierarchy hn
WHERE id = _id
)
FROM (
SELECT @r := 1000000,
@level := 0
) vars,
t_hierarchy hc
WHERE @r IS NOT NULL
) hc
JOIN t_hierarchy hp
ON hp.id = hc._id
ORDER BY
level DESC