Description:
A query with user-defined variables maybe not use an index to scan records in MySQL 8.0(including MySQL 8.0.30), although there is a suitable index for the query condition.
Query in MySQL 8.0: select * from (SELECT @r AS _id, (SELECT @r := sup_local_code FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_local_code, (SELECT a.local_name FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_node_name, (SELECT a.lv FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_lv FROM (SELECT @r :='442000001000') as vars, p_locations AS h) as t where t.sup_lv > -1;
The query always scans records using the table scan, which exhibits poor performance.
How to repeat:
CREATE TABLE `p_locations` (
`id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'primary',
`flag` varchar(6) DEFAULT NULL,
`local_code` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'code',
`local_name` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'name',
`lv` int DEFAULT NULL COMMENT 'levle',
`sup_local_code` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'parent code',
`url` varchar(60) DEFAULT NULL,
`path` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_p_locations_id` (`id`),
KEY `index_p_locations_local_code` (`local_code`),
KEY `index_p_locations_local_name` (`local_name`),
KEY `index_p_locations_sup_local_code` (`sup_local_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
In MySQL 8.0:
explain select * from (SELECT @r AS _id, (SELECT @r := sup_local_code FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_local_code, (SELECT a.local_name FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_node_name, (SELECT a.lv FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_lv FROM (SELECT @r :='442000001000') as vars, p_locations AS h) as t where t.sup_lv > -1;
+----+--------------------+------------+------------+--------+------------------------------+------------------------------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+--------+------------------------------+------------------------------+---------+------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| 2 | DERIVED | <derived6> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | h | NULL | index | NULL | index_p_locations_local_code | 93 | NULL | 1 | 100.00 | Using index |
| 6 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 5 | DEPENDENT SUBQUERY | a | NULL | ALL | index_p_locations_local_code | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 4 | DEPENDENT SUBQUERY | a | NULL | ALL | index_p_locations_local_code | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | a | NULL | ALL | index_p_locations_local_code | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+--------------------+------------+------------+--------+------------------------------+------------------------------+---------+------+------+----------+----------------+
In MySQL 5.7:
mysql> explain select * from (SELECT @r AS _id, (SELECT @r := sup_local_code FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_local_code, (SELECT a.local_name FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_node_name, (SELECT a.lv FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_lv FROM (SELECT @r:='442000001000') as vars, p_locations AS h) as t where t.sup_lv > -1;
+----+--------------------+------------+------------+--------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+--------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| 2 | DERIVED | <derived6> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | h | NULL | index | NULL | index_p_locations_local_code | 93 | NULL | 1 | 100.00 | Using index |
| 6 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 5 | DEPENDENT SUBQUERY | a | NULL | ref | index_p_locations_local_code | index_p_locations_local_code | 93 | func | 1 | 100.00 | Using index condition |
| 4 | DEPENDENT SUBQUERY | a | NULL | ref | index_p_locations_local_code | index_p_locations_local_code | 93 | func | 1 | 100.00 | Using index condition |
| 3 | DEPENDENT SUBQUERY | a | NULL | ref | index_p_locations_local_code | index_p_locations_local_code | 93 | func | 1 | 100.00 | Using index condition |
+----+--------------------+------------+------------+--------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
