Description:
The handler_ status counters are used for monitoring, for example, to detect full table scans, but these counters are not incremented when a COUNT(*) without a WHERE clause is used on an InnoDB table in MySQL 8.
I imagine this problem is related to the optimized COUNT(*) implementation that is used for InnoDB (parallel implementation) on MySQL 8. When MyISAM uses fast count(*) it does not read from the table, so it shouldn't increment status counters for COUNT(*) w/out a WHERE clause. InnoDB, however, still does read from the table, but the counters are not updated.
How to repeat:
mysql> drop database if exists test;
Query OK, 1 row affected (0.56 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table handler_test(c1 int primary key) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into handler_test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
-- should increment status variables (faster count(*) does read rows!)
mysql> select count(*) from handler_test;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
-- status variables are not changed
mysql> show session status like '%handler%read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.02 sec)
-- should increment status variables
mysql> select count(*) from handler_test where c1 > 0;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
-- status variables ARE incremented
mysql> show session status like '%handler%read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 10 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.02 sec)
mysql> alter table handler_test engine=myisam;
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
-- status variables SHOULD NOT incremented (fast count(*) reads no rows)
mysql> select count(*) from handler_test;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
-- results are correct
mysql> show session status like '%handler%read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.02 sec)
-- should increment status variables
mysql> select count(*) from handler_test where c1 > 0;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
-- status variables ARE incremented
mysql> show session status like '%handler%read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 10 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.02 sec)
Suggested fix:
Increment the status counters in InnoDB parallel count(*) implementation, since rows are actually read.
