索引嵌套关联 mysql,MySQL&嵌套集：慢JOIN（不使用索引）

最新推荐文章于 2022-04-18 19:24:49 发布

非著名程序~~~

最新推荐文章于 2022-04-18 19:24:49 发布

阅读量76

点赞数

文章标签：索引嵌套关联 mysql

I have two tables:

localities:

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;

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.

Here is my try:

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.

I consulted EXPLAIN:

+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+

+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+

| 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)

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

UPDATE:

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

UPDATE 2: Column names instead of the asterisk

Updated query:

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";

Updated EXAPLAIN:

+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+

| 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) |

Any help appreciated.

解决方案

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