Mysql 8.0 not using the correct index causing slow execution compared to Mysql 5.6-CodePudding

I am migrating from mysql 5.6(innodb) to mysql 8.0(innodb). For one of the query(used for pagination) the execution time for MySql 8 is slower than Mysql 5.6, probably because it's not using the correct index.

Mysql 5.6

CREATE TABLE `contest_user_team_mapping` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_match_mapping_id` bigint(20) NOT NULL,
`contest_id` bigint(20) NOT NULL,
`entry_fee_type` int(11) DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`match_id` bigint(20) DEFAULT NULL,
`additional_info` text,
`user_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `contest_user` (`contest_id`,`user_match_mapping_id`),
KEY `idx_contest_id` (`contest_id`),
KEY `idx_updated_at` (`updated_at`),
KEY `idx_user_match_mapping_id` (`user_match_mapping_id`),
KEY `indx_comp` (`contest_id`,`user_match_mapping_id`),
KEY `idx_match_id` (`match_id`),
KEY `idx_user_id` (`user_id`)
)   ENGINE=InnoDB AUTO_INCREMENT=533924175 DEFAULT CHARSET=latin1


mysql> explain     select  id, contest_id
    from  contest_user_team_mapping
    where  id >=1
      and  match_id = 1745
    limit  100;

 ---- ------------- --------------------------- ------------- ---------------------- ---------------------- --------- ------ --------- ---------------------------------------------------- 
| id | select_type | table                     | type        | possible_keys        | key                  | key_len | ref  | rows    | Extra                                              |
 ---- ------------- --------------------------- ------------- ---------------------- ---------------------- --------- ------ --------- ---------------------------------------------------- 
|  1 | SIMPLE      | contest_user_team_mapping | index_merge | PRIMARY,idx_match_id | idx_match_id,PRIMARY | 17,8    | NULL | 4869005 | Using intersect(idx_match_id,PRIMARY); Using where |
 ---- ------------- --------------------------- ------------- ---------------------- ---------------------- --------- ------ --------- ----------------------------------------------------

Mysql 8.0

CREATE TABLE `contest_user_team_mapping` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_match_mapping_id` bigint NOT NULL,
`contest_id` bigint NOT NULL,
`entry_fee_type` int DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`match_id` bigint DEFAULT NULL,
`additional_info` text,
`user_id` bigint DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `contest_user` (`contest_id`,`user_match_mapping_id`),
 KEY `idx_contest_id` (`contest_id`),
 KEY `idx_updated_at` (`updated_at`),
 KEY `idx_match_id` (`match_id`),
 KEY `idx_user_id` (`user_id`)


mysql> explain   select id, contest_id
    from  contest_user_team_mapping
    where  match_id = 1974
      and  id>=1
    limit  100;

 ---- ------------- --------------------------- ------------ ------- ---------------------- --------- --------- ------ ---------- ---------- ------------- 
| id | select_type | table                     | partitions | type  | possible_keys        | key     | key_len | ref  | rows     | filtered | Extra       |
 ---- ------------- --------------------------- ------------ ------- ---------------------- --------- --------- ------ ---------- ---------- ------------- 
|  1 | SIMPLE      | contest_user_team_mapping | NULL       | range | PRIMARY,idx_match_id | PRIMARY | 8       | NULL | 97063072 |     0.02 | Using where |
 ---- ------------- --------------------------- ------------ ------- ---------------------- --------- --------- ------ ---------- ---------- ------------- 
1 row in set, 1 warning (0.00 sec)

mysql>     select  /*  NO_RANGE_OPTIMIZATION(contest_user_team_mapping) INDEX_MERGE(contest_user_team_mapping)*/
        id,
        contest_id
    from  contest_user_team_mapping FORCE INDEX FOR
    JOIN  (idx_match_id,PRIMARY)
    where  id >=1
      and  match_id = 1974
    limit  1;

 ---- ------------- --------------------------- ------------ ------ ---------------------- -------------- --------- ------- ------- ---------- ----------------------- 
| id | select_type | table                     | partitions | type | possible_keys        | key          | key_len | ref   | rows  | filtered | Extra                 |
 ---- ------------- --------------------------- ------------ ------ ---------------------- -------------- --------- ------- ------- ---------- ----------------------- 
|  1 | SIMPLE      | contest_user_team_mapping | NULL       | ref  | PRIMARY,idx_match_id | idx_match_id | 9       | const | 34566 |    33.33 | Using index condition |
 ---- ------------- --------------------------- ------------ ------ ---------------------- -------------- --------- ------- ------- ---------- ----------------------- 
1 row in set, 1 warning (0.00 sec)

For Mysql 8.0 it does not seems to be using the index idx_match_id,PRIMARY which is probably causing it to be slow. I also tried to use force index but that does not seem to help either.

CodePudding user response：

Replace

KEY `idx_match_id` (`match_id`),

with

INDEX(match_id, id, contest_id)

That would be "covering" and is likely to be "correctly" picked in both 5.6 and 8.0.

CodePudding user response：

Add match_ id，contest_ ID union index