Description:
A simple count(*) query on information_schema.innodb_tables table is taking a very long time in MySQL 8.0.
MySQL 8.0: Query execution time: 6 min 19.48 sec
mysql> select count(*) from information_schema.innodb_tables; show status where variable_name like 'Hander%' or variable_name like 'Created%';
+----------+
| count(*) |
+----------+
| 402561 |
+----------+
1 row in set (6 min 19.48 sec)
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.01 sec)
mysql> explain select count(*) from information_schema.innodb_tables;
Current database: *** NONE ***
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | innodb_tables | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
MySQL 5.7: Query execution time: 4.43 sec
mysql> select count(*) from information_schema.innodb_sys_tables;
Current database: *** NONE ***
+----------+
| count(*) |
+----------+
| 1197109 |
+----------+
1 row in set (4.43 sec)
mysql> explain select count(*) from information_schema.innodb_sys_tables;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | innodb_sys_tables | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
How to repeat:
This difference in execution time is consistent through multiple executions across different servers.
Suggested fix:
NA
