Bug #76280 Select count(*) in MySQL5.7 is slower than in MySQL5.6 in case of out of memory
Submitted: 12 Mar 2015 8:55 Modified: 12 Oct 2016 9:29
Reporter: 頭突 大井 Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.7.5-m15 OS:Linux
Assigned to: CPU Architecture:Any
Tags: select count, slow

[12 Mar 2015 8:55] 頭突 大井
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.
[12 Oct 2016 9:29] MySQL Verification Team
Hello!

Thank you for the report.
This is duplicate of Bug #80580, please see Bug #80580

Thanks,
Umesh