Bug #80580 count(*) much slower on 5.7 than 5.6
Submitted: 2 Mar 2016 6:43 Modified: 16 Feb 2017 18:36
Reporter: Qi Xiaobin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.2, 5.7.10, 5.7.11 OS:Any (Amazon Linux AMI)
Assigned to: CPU Architecture:Any
Tags: count, query performance, WL#6742

[2 Mar 2016 6:43] Qi Xiaobin
Description:
On Amazon RDS, 5.7.10:
mysql> select count(*) from poi;
+----------+
| count(*) |
+----------+
|  1185416 |
+----------+
1 row in set (22.70 sec)

mysql> explain select count(*) from poi\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.01 sec)

On Amazon RDS 5.6.22:

mysql> select count(*) from poi;
+----------+
| count(*) |
+----------+
|  1185416 |
+----------+
1 row in set (0.32 sec)

mysql> explain select count(*) from poi\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: poi
         type: index
possible_keys: NULL
          key: spot_index_id
      key_len: 4
          ref: NULL
         rows: 886952
        Extra: Using index
1 row in set (0.06 sec)

Mysql 5.7 seems not using any index on "select count(*)" query (or something like "select count(id)" query) on any table.  And that makes it much slower than 5.6.
Don't know why.

How to repeat:
on Mysql 5.7.10:

explain select count(*) from any_table

and can find that it does not use any index
[4 Apr 2016 9:34] Umesh Shastry
Hello Xiaobin,

Thank you for the report.

Thanks,
Umesh
[24 Aug 2016 7:13] Daniƫl van Eeden
Could you add some EXPLAIN FORMAT=JSON output? That gives some more info not available in the traditional output.

And if possible the output of "show session status like 'Handler\_%';" from before and after running the query. (you might want to run 'flush status' before doing it)
[4 Sep 2016 9:29] Umesh Shastry
Hi Xiaobin,

Could you please provide exact CREATE statement of the table poi? Please mark it as private after posting here.

Thanks,
Umesh
[12 Oct 2016 9:29] Umesh Shastry
Bug #76280 marked as duplicate of this
[12 Oct 2016 10:00] Valeriy Kravchuk
In case anyone misses public test case, here it is:

drop table if exists tdummy;
create table tdummy(id int auto_increment primary key, c1 int, key(c1)) engine=InnoDB;
insert into tdummy(c1) values(1);
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
insert into tdummy(c1) select 2 from tdummy;
alter table tdummy add column c2 char(200) default 'a';
select version();

explain select count(*) from tdummy;
show status like 'Innodb_buffer_pool_read%';
select count(*) from tdummy;
show status like 'Innodb_buffer_pool_read%';

On 5.7.15 (started with --no-defaults) I have:

...
mysql> insert into tdummy(c1) select 2 from tdummy;
Query OK, 524288 rows affected (9.57 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> alter table tdummy add column c2 char(200) default 'a';
Query OK, 0 rows affected (53.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.15    |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> explain select count(*) from tdummy;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show status like 'Innodb_buffer_pool_read%';
+---------------------------------------+----------+
| Variable_name                         | Value    |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0        |
| Innodb_buffer_pool_read_ahead         | 3708     |
| Innodb_buffer_pool_read_ahead_evicted | 55       |
| Innodb_buffer_pool_read_requests      | 14145760 |
| Innodb_buffer_pool_reads              | 59163    |
+---------------------------------------+----------+
5 rows in set (0.00 sec)

mysql> select count(*) from tdummy;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.52 sec)

mysql> show status like 'Innodb_buffer_pool_read%';
+---------------------------------------+----------+
| Variable_name                         | Value    |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0        |
| Innodb_buffer_pool_read_ahead         | 3900     |
| Innodb_buffer_pool_read_ahead_evicted | 110      |
| Innodb_buffer_pool_read_requests      | 14290454 |
| Innodb_buffer_pool_reads              | 71263    |
+---------------------------------------+----------+
5 rows in set (0.00 sec)

Note time to execute and the difference in Innodb_buffer_pool_read_requests (144694) and Innodb_buffer_pool_reads. 

On some 5.6 (started with --no-defaults) I have:

...
mysql> insert into tdummy(c1) select 2 from tdummy;
Query OK, 524288 rows affected (4.50 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> alter table tdummy add column c2 char(200) default 'a';
Query OK, 0 rows affected (39.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.6.28-76.1 |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> explain select count(*) from tdummy;
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | tdummy | index | NULL          | c1   | 5       | NULL | 1032785 | Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> show status like 'Innodb_buffer_pool_read%';
+---------------------------------------+----------+
| Variable_name                         | Value    |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0        |
| Innodb_buffer_pool_read_ahead         | 0        |
| Innodb_buffer_pool_read_ahead_evicted | 0        |
| Innodb_buffer_pool_read_requests      | 13928250 |
| Innodb_buffer_pool_reads              | 241      |
+---------------------------------------+----------+
5 rows in set (0.00 sec)

mysql> select count(*) from tdummy;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.24 sec)

mysql> show status like 'Innodb_buffer_pool_read%';
+---------------------------------------+----------+
| Variable_name                         | Value    |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0        |
| Innodb_buffer_pool_read_ahead         | 0        |
| Innodb_buffer_pool_read_ahead_evicted | 0        |
| Innodb_buffer_pool_read_requests      | 14045702 |
| Innodb_buffer_pool_reads              | 241      |
+---------------------------------------+----------+
5 rows in set (0.00 sec)

Note time to execute and difference in read requests: 117452

That's what we have. To summarize, it seems 5.7's "optimization" really requires full table scan or something of that kind, even if EXPLAIN says "Select tables optimized away".
[27 Jan 2017 22:15] Debra Bartling
This is also an issue for me on Amazon RDS MySQL 5.7.16 after upgrading from MySQL 5.6: Large table (21 million rows) with 21 indexes.

Comparing the same select count(*) query on 5.6 and 5.7 versions of the same database:

5.6.22: It completed in 47.50 seconds on a cold database
5.7.16: Still running 50 minutes later, output from show processlist:

Command: Query
Time: 3054
State: optimizing
Info: select count(*) from ...

Alternative approaches to the query also take twice as long, for example: 

select count(anykey) from ...
[16 Feb 2017 18:36] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.18, 8.0.1 release, and here's the changelog entry:

SELECT COUNT(*) performance regressed in some cases due to a modification
introduced in MySQL 5.7.2 that caused InnoDB to count rows by traversing
the clustered index instead of a smaller secondary index. The modification
was reverted. 

Thank you for the bug report.
[23 Feb 2017 17:19] Daniel Price
Posted by developer:
 
The changelog entry noted above now only appears in the 5.7.18 release notes.
[25 May 6:01] Umesh Shastry
Bug #80191 marked as duplicate of this one