Bug #117867 use distinct+left join, MySQL 8.0 excute long time than MySQL 5.7
Submitted: 3 Apr 8:32 Modified: 9 Apr 10:18
Reporter: haizhen xue Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.41 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[3 Apr 8:32] haizhen xue 
Description:
Executing this SQL statement on MySQL 8.0 takes 8.68s：
mysql> SELECT DISTINCT juua.unit_oid FROM JDB_USER_UNIT_AUTH juua LEFT JOIN jdb_role_menu r ON juua.system_position_oid = r.role_id WHERE juua.user_id = 'xxx';
+----------+
| unit_oid |
+----------+
|        1 |
+----------+
1 row in set (8.68 sec)

Executing this SQL statement on MySQL 5.7 takes 0s：
mysql> SELECT DISTINCT juua.unit_oid FROM JDB_USER_UNIT_AUTH juua LEFT JOIN jdb_role_menu r ON juua.system_position_oid = r.role_id WHERE juua.user_id = 'xxx';
+----------+
| unit_oid |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

How to repeat:
create database test;
uset test;
source test.sql
SELECT DISTINCT juua.unit_oid FROM JDB_USER_UNIT_AUTH juua LEFT JOIN jdb_role_menu r ON juua.system_position_oid = r.role_id WHERE juua.user_id = 'xxx';
[3 Apr 9:15] haizhen xue 
MYSQL 8.0, read examine 18011 rows.
mysql> show status like '%Handler_read_next%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Handler_read_next | 144481 |
+-------------------+--------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> explain analyze SELECT DISTINCT juua.unit_oid FROM JDB_USER_UNIT_AUTH juua LEFT JOIN jdb_role_menu r ON juua.system_position_oid = r.role_id WHERE juua.user_id = 'xxx';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=2.53..4.8 rows=8) (actual time=19..19 rows=1 loops=1)
    -> Temporary table with deduplication  (cost=2.2..2.2 rows=8) (actual time=19..19 rows=1 loops=1)
        -> Nested loop left join  (cost=1.4 rows=8) (actual time=0.0411..14.3 rows=18010 loops=1)
            -> Covering index lookup on juua using USER_UNIT_AUTH_IDX_JOINT1 (user_id='xxx')  (cost=0.35 rows=1) (actual time=0.0254..0.0292 rows=1 loops=1)
            -> Covering index lookup on r using IDX_ROLE_ID (role_id=juua.system_position_oid)  (cost=1.05 rows=8) (actual time=0.0144..12.7 rows=18010 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> show status like '%Handler_read_next%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Handler_read_next | 162492 |
+-------------------+--------+
1 row in set (0.00 sec)

MySQL 5.7 read examine 1 rows.
mysql> show status like '%Handler_read_next%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 0     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT DISTINCT juua.unit_oid FROM JDB_USER_UNIT_AUTH juua LEFT JOIN jdb_role_menu r ON juua.system_position_oid = r.role_id WHERE juua.user_id = 'xxx';
+----------+
| unit_oid |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> show status like '%Handler_read_next%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 1     |
+-------------------+-------+
1 row in set (0.00 sec)
[3 Apr 9:19] haizhen xue 
insert into jdb_user_unit_auth values('xxx',1,'xxx','x','xxx','xxx',1,'x','x','x');

loops 18011,insert 18011 same rows:
insert into jdb_role_menu values('xxx',1,'xxx','2017-08-08 12:12:12','xxx','2017-08-08 12:12:12','xxx','xxx','x','x','x');
[3 Apr 9:21] haizhen xue 
table:
CREATE TABLE `jdb_role_menu` (
  `menu_code` varchar(20) DEFAULT NULL COMMENT 'TRIAL',
  `role_id` bigint NOT NULL COMMENT 'TRIAL',
  `created_by_code` varchar(20) DEFAULT NULL COMMENT 'TRIAL',
  `created_date` datetime DEFAULT NULL COMMENT 'TRIAL',
  `updated_by_code` varchar(20) DEFAULT NULL COMMENT 'TRIAL',
  `updated_date` datetime DEFAULT NULL COMMENT 'TRIAL',
  `created_by_name` varchar(20) DEFAULT NULL COMMENT 'TRIAL',
  `updated_by_name` varchar(20) DEFAULT NULL COMMENT 'TRIAL',
  `trial753` char(1) DEFAULT NULL COMMENT 'TRIAL',
  `trial680` char(1) DEFAULT NULL COMMENT 'TRIAL',
  `trial755` char(1) DEFAULT NULL COMMENT 'TRIAL',
  KEY `IDX_ROLE_ID_MENU_CODE` (`menu_code`,`role_id`),
  KEY `IDX_ROLE_ID` (`role_id`) USING BTREE,
  KEY `IDX_MENU_CODE` (`menu_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='TRIAL';

CREATE TABLE `jdb_user_unit_auth` (
  `user_id` varchar(20) NOT NULL COMMENT 'TRIAL',
  `unit_oid` bigint NOT NULL COMMENT 'TRIAL',
  `unit_name` varchar(1000) NOT NULL COMMENT 'TRIAL',
  `unit_status` varchar(2) DEFAULT NULL COMMENT 'TRIAL',
  `unit_kind` varchar(20) DEFAULT NULL COMMENT 'TRIAL',
  `unit_category_code` varchar(20) DEFAULT NULL COMMENT 'TRIAL',
  `system_position_oid` bigint NOT NULL COMMENT 'TRIAL',
  `trial847` char(1) DEFAULT NULL COMMENT 'TRIAL',
  `trial739` char(1) DEFAULT NULL COMMENT 'TRIAL',
  `trial859` char(1) DEFAULT NULL COMMENT 'TRIAL',
  KEY `INDEX_POSITION_ID` (`system_position_oid`),
  KEY `INDEX_UNIT_OID` (`unit_oid`),
  KEY `USER_UNIT_AUTH_IDX_JOINT1` (`user_id`,`unit_oid`,`system_position_oid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='TRIAL';
[9 Apr 8:38] MySQL Verification Team 
Hello haizhen xue,

Thank you for the report and feedback.
Could you please attach "test.sql" to the bug report? Thank you.

regards,
Umesh
[9 Apr 10:18] MySQL Verification Team 
Hello haizhen xue,

Thank you for the report and test case.

regards,
Umesh