Bug #100020 unexpect Rows_examined in mysql 8.0 when full table count(*) query
Submitted: 28 Jun 2020 3:31 Modified: 6 Jul 2020 12:58
Reporter: Ye Jinrong Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Logging Severity:S3 (Non-critical)
Version:8.0, 8.0.20 OS:Any
Assigned to: CPU Architecture:Any
Tags: count(*), MySQL 8.0, regression

[28 Jun 2020 3:31] Ye Jinrong
Description:
Under MySQL 8.0, a full innodb table count(*), the slow query log shows it's Rows_examined is 0.
But before MySQL 8.0, and under the same condition, the slow query log shows it's Rows_examined is not 0.
It is so much confused.

How to repeat:
Under MySQL 8.0
mysql> set session long_query_time = 0;
mysql> set session min_examined_row_limit = 0;
mysql> desc select count(*) from ssid;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | ssid  | NULL       | index | NULL          | k1   | 4       | NULL | 792124 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+

mysql> show create table ssid\G
CREATE TABLE `ssid` (
  `id` int unsigned NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL DEFAULT '',
  `aid` int unsigned NOT NULL AUTO_INCREMENT,
  `nid` int unsigned GENERATED ALWAYS AS ((`id` + 1)) VIRTUAL NOT NULL,
  `nnid` int unsigned GENERATED ALWAYS AS ((`id` + 1)) STORED NOT NULL,
  PRIMARY KEY (`aid`),
  KEY `k1` (`id`)
) ENGINE=InnoDB;

mysql> select count(*) from ssid;
+----------+
| count(*) |
+----------+
|   799994 |
+----------+
1 row in set (0.26 sec)

slow query log is:
# Time: 2020-06-28T03:18:12.116364Z
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 0.257454  Lock_time: 0.000159 Rows_sent: 1  Rows_examined: 0 Thread_id: 9 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 61 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2020-06-28T03:18:11.858910Z End: 2020-06-28T03:18:12.116364Z
SET timestamp=1593314291;
select count(*) from ssid;

even though full table count(*) with "where id>=0" condition (filed 'id' has defined as 'int unsigned NOT NULL')

select count(*) from ssid where id>=0;
+----------+
| count(*) |
+----------+
|   799994 |
+----------+
1 row in set (0.09 sec)

it still shows Rows_examined = 0
# Time: 2020-06-28T03:19:32.143910Z
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 0.092063  Lock_time: 0.000173 Rows_sent: 1  Rows_examined: 0 Thread_id: 9 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 61 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2020-06-28T03:19:32.051847Z End: 2020-06-28T03:19:32.143910Z
SET timestamp=1593314372;
select count(*) from ssid where id>=0;

but when under MySQL 5.6, Rows_examined always shows not 0 (i think it is righ result as expect)
# Time: 200628 11:27:09
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 0.004183  Lock_time: 0.000052 Rows_sent: 1  Rows_examined: 23296
SET timestamp=1593314829;
select count(*) from t1;

# Time: 200628 11:28:17
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 0.004602  Lock_time: 0.000084 Rows_sent: 1  Rows_examined: 23296
SET timestamp=1593314897;
select count(*) from t1 force index(primary);

Suggested fix:
change the Rows_examined counter, and make it work right as expect as usual, thanks.
[29 Jun 2020 6:53] MySQL Verification Team
Hello Ye Jinrong,

Thank you for the report and feedback!

regards,
Umesh
[29 Jun 2020 8:01] Øystein Grøvlen
Both in 5.7 and 8.0, rows_examined only reflect how many rows are handled by the server.  Operations that are pushed down to the storage engine, like counting all rows of a table in 8.0, will not be reflected in rows_examined.  I would think Index Condition Pushdown has had the same issues since 5.6.
[6 Jul 2020 12:01] Ståle Deraas
We agree that the semantics of this requires quite some knowledge on how internals of the product work, as we see in the explanation by Øystein. Still we don't consider it a bug.