Reputation: 101

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

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.

Upvotes: 2