Description:
Select count(*) in MySQL5.7 is slower than in MySQL5.6, in case of table-data is not in memory.
How to repeat:
mysql> show variables like 'innodb_buffer_pool_size' ;
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 | --> 1G
+-------------------------+------------+
1. [MySQL5.7.5] & [MySQL5.6.16]
CREATE TABLE `test` (
`i1` bigint(20) NOT NULL AUTO_INCREMENT,
`i2` tinyint(3) unsigned NOT NULL,
`i3` int(10) unsigned NOT NULL,
`txt1` text,
`txt2` text,
`txt3` text,
`txt4` text,
`txt5` text,
`txt6` text,
`txt7` text,
`txt8` text,
`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`i1`),
KEY `key2` (`i2`),
KEY `key3` (`i3`),
KEY `key4` (`dt`)
) ENGINE=InnoDB AUTO_INCREMENT=1966051 DEFAULT CHARSET=utf8mb4 ;
[MySQL5.7.5]
2. mysql> insert into test ... ;
Records: 1910336 Duplicates: 0 Warnings: 0
mysql> show table status like 'test' \G
*************************** 1. row ***************************
Name: test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1755455
Avg_row_length: 752
Data_length: 1321205760 --> over 1G(innodb_buffer_pool_size)
Max_data_length: 0
Index_length: 117145600
Data_free: 7340032
Auto_increment: 1966051
Create_time: 2015-03-12 16:29:46
Update_time: 2015-03-12 16:41:18
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
3. mysql> select count(*) from test ;
+----------+
| count(*) |
+----------+
| 1910336 |
+----------+
1 row in set (1 min 51.91 sec)
mysql> select count(*) from test ;
+----------+
| count(*) |
+----------+
| 1910336 |
+----------+
1 row in set (1 min 40.33 sec)
mysql> explain select count(*) from test ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
[MySQL5.6.16]
2'. mysql> insert into test ... ;
Records: 1910336 Duplicates: 0 Warnings: 0
mysql> show table status like 'test' \G
*************************** 1. row ***************************
Name: test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1946457
Avg_row_length: 735
Data_length: 1431306240 --> over 1G(innodb_buffer_pool_size)
Max_data_length: 0
Index_length: 125566976
Data_free: 7340032
Auto_increment: 1966051
Create_time: 2015-03-12 16:29:16
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
3'. mysql> select count(*) from test ;
+----------+
| count(*) |
+----------+
| 1910336 |
+----------+
1 row in set (45.82 sec)
mysql> select count(*) from test ; <-- Index-data is in memory
+----------+
| count(*) |
+----------+
| 1910336 |
+----------+
1 row in set (0.33 sec)
mysql> explain select count(*) from test ;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | test | index | NULL | key2 | 1 | NULL | 1946457 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
Suggested fix:
I am not read source-code yet.
Description: Select count(*) in MySQL5.7 is slower than in MySQL5.6, in case of table-data is not in memory. How to repeat: mysql> show variables like 'innodb_buffer_pool_size' ; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1073741824 | --> 1G +-------------------------+------------+ 1. [MySQL5.7.5] & [MySQL5.6.16] CREATE TABLE `test` ( `i1` bigint(20) NOT NULL AUTO_INCREMENT, `i2` tinyint(3) unsigned NOT NULL, `i3` int(10) unsigned NOT NULL, `txt1` text, `txt2` text, `txt3` text, `txt4` text, `txt5` text, `txt6` text, `txt7` text, `txt8` text, `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`i1`), KEY `key2` (`i2`), KEY `key3` (`i3`), KEY `key4` (`dt`) ) ENGINE=InnoDB AUTO_INCREMENT=1966051 DEFAULT CHARSET=utf8mb4 ; [MySQL5.7.5] 2. mysql> insert into test ... ; Records: 1910336 Duplicates: 0 Warnings: 0 mysql> show table status like 'test' \G *************************** 1. row *************************** Name: test Engine: InnoDB Version: 10 Row_format: Compact Rows: 1755455 Avg_row_length: 752 Data_length: 1321205760 --> over 1G(innodb_buffer_pool_size) Max_data_length: 0 Index_length: 117145600 Data_free: 7340032 Auto_increment: 1966051 Create_time: 2015-03-12 16:29:46 Update_time: 2015-03-12 16:41:18 Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) 3. mysql> select count(*) from test ; +----------+ | count(*) | +----------+ | 1910336 | +----------+ 1 row in set (1 min 51.91 sec) mysql> select count(*) from test ; +----------+ | count(*) | +----------+ | 1910336 | +----------+ 1 row in set (1 min 40.33 sec) mysql> explain select count(*) from test ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ [MySQL5.6.16] 2'. mysql> insert into test ... ; Records: 1910336 Duplicates: 0 Warnings: 0 mysql> show table status like 'test' \G *************************** 1. row *************************** Name: test Engine: InnoDB Version: 10 Row_format: Compact Rows: 1946457 Avg_row_length: 735 Data_length: 1431306240 --> over 1G(innodb_buffer_pool_size) Max_data_length: 0 Index_length: 125566976 Data_free: 7340032 Auto_increment: 1966051 Create_time: 2015-03-12 16:29:16 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) 3'. mysql> select count(*) from test ; +----------+ | count(*) | +----------+ | 1910336 | +----------+ 1 row in set (45.82 sec) mysql> select count(*) from test ; <-- Index-data is in memory +----------+ | count(*) | +----------+ | 1910336 | +----------+ 1 row in set (0.33 sec) mysql> explain select count(*) from test ; +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | test | index | NULL | key2 | 1 | NULL | 1946457 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ Suggested fix: I am not read source-code yet.