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.
