| 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: | |
| 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 | ||
[4 Apr 2016 9:34]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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 2018 6:01]
MySQL Verification Team
Bug #80191 marked as duplicate of this one
[3 Sep 2019 15:58]
Bhushan Mer
Recently we were planning to migrate MySQL 5.7 to 8. During benchmarking, we noticed the same issue on Innodb Version 8.0.17. It's working perfectly for SQL_CALC_FOUND_ROWS & count(distinct pk) but whenever tried to use count(*) or count(0) to get count it's fails to use index & going through whole table scan. Can you please guide whether it's a bug or some misconfiguration.
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