Description:
First of all,thank you for taking the time to loot at my question.
The following is my problem.
When a table is created as partition table,the count info such as count_read,count_star in table_io_waits_summary_by_table and table_io_waits_summary_by_index_usage can't update as expected in some cases.
As I tested,the follow cases will not update count info in PS io wait summary table:
1. range query using secondary index or primary ,include where condition is col_name in ('a','b') that in more than one value
While the follow situations ,the count info is updated correctly:
1. DML SQL
2. equal conditions using secondary index or primary key
3. sql no using index
Here,I list my test sql, the details can be seen in "How to repeat":
Observe the count info:
select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb';
SQL:
1. cases updatting count info correctly not as expected
Range query using primary or secondary index：
select * from t where id<=3;
select * from t where id in (1,2);
2. cases updating count info correctly
equal condition: select * from t where id=1;
full table scan: SELECT * FROM t;
How to repeat:
1. my test table structure
CREATE TABLE `t` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`col1` int DEFAULT NULL,
`col2` varchar(20) DEFAULT NULL,
`partition_key` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`,`partition_key`),
KEY `idx_col2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`partition_key`))
(PARTITION p20240528 VALUES LESS THAN (739400) ENGINE = InnoDB,
PARTITION p20240529 VALUES LESS THAN (739401) ENGINE = InnoDB,
PARTITION p20240530 VALUES LESS THAN (739402) ENGINE = InnoDB,
PARTITION p20240531 VALUES LESS THAN (739403) ENGINE = InnoDB,
PARTITION p20240601 VALUES LESS THAN (739404) ENGINE = InnoDB,
PARTITION p20240602 VALUES LESS THAN (739405) ENGINE = InnoDB,
PARTITION p20240603 VALUES LESS THAN (739406) ENGINE = InnoDB,
PARTITION p20240604 VALUES LESS THAN (739407) ENGINE = InnoDB,
PARTITION p20240605 VALUES LESS THAN (739408) ENGINE = InnoDB,
PARTITION p20240606 VALUES LESS THAN (739409) ENGINE = InnoDB,
PARTITION p20240607 VALUES LESS THAN (739410) ENGINE = InnoDB,
PARTITION p20240608 VALUES LESS THAN (739411) ENGINE = InnoDB,
PARTITION p20240609 VALUES LESS THAN (739412) ENGINE = InnoDB,
PARTITION p20240610 VALUES LESS THAN (739413) ENGINE = InnoDB,
PARTITION p20240611 VALUES LESS THAN (739414) ENGINE = InnoDB,
PARTITION p20240612 VALUES LESS THAN (739415) ENGINE = InnoDB,
PARTITION p20240613 VALUES LESS THAN (739416) ENGINE = InnoDB,
PARTITION p20240614 VALUES LESS THAN (739417) ENGINE = InnoDB,
PARTITION p20240615 VALUES LESS THAN (739418) ENGINE = InnoDB,
PARTITION p20240616 VALUES LESS THAN (739419) ENGINE = InnoDB,
PARTITION p20240617 VALUES LESS THAN (739420) ENGINE = InnoDB,
PARTITION p20240618 VALUES LESS THAN (739421) ENGINE = InnoDB,
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
2.insert datas -- this step the count info is updated correctly
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TABLE | testdb | t | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
2 rows in set (0.01 sec)
mysql>
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.01 sec)
mysql> insert into t values(null,1,'abc','2024-06-12'),(null,2,'abc','2024-06-12'),(null,3,'abc','2024-06-12'),(null,1,'bcd','2024-06-11'),(null,2,'bcd','2024-06-11'),(null,2,'bcd','2024-06-10');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | PRIMARY | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TABLE | testdb | t | NULL | 6 | 0 | 6 | 0 | 6 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 6 | 0 | 6 | 0 | 6 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
3. the count info is updated in the following cases.
Please attention,here I just list two cases:
a. range query using primary key,when using secondary key,it's the same result.But I don't display here when using secondary key.
b. full table scan
mysql> TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;
Query OK, 0 rows affected (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | PRIMARY | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TABLE | testdb | t | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
mysql> select * from t where id=1;
+----+------+------+---------------------+
| id | col1 | col2 | partition_key |
+----+------+------+---------------------+
| 1 | 1 | abc | 2024-06-12 00:00:00 |
+----+------+------+---------------------+
1 row in set (0.00 sec)
-- the count_star increased by 2,not 1
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 2 | 2 | 0 | 2 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | PRIMARY | 2 | 2 | 0 | 2 | 0 | 0 | 0 |
| TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TABLE | testdb | t | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)
-- table scan : the count_star increased by total rows number in user table
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 2 | 2 | 0 | 2 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | PRIMARY | 2 | 2 | 0 | 2 | 0 | 0 | 0 |
| TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TABLE | testdb | t | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM t;
+----+------+------+---------------------+
| id | col1 | col2 | partition_key |
+----+------+------+---------------------+
| 6 | 2 | bcd | 2024-06-10 00:00:00 |
| 4 | 1 | bcd | 2024-06-11 00:00:00 |
| 5 | 2 | bcd | 2024-06-11 00:00:00 |
| 1 | 1 | abc | 2024-06-12 00:00:00 |
| 2 | 2 | abc | 2024-06-12 00:00:00 |
| 3 | 3 | abc | 2024-06-12 00:00:00 |
+----+------+------+---------------------+
6 rows in set (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 8 | 8 | 0 | 8 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | PRIMARY | 2 | 2 | 0 | 2 | 0 | 0 | 0 |
| TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TABLE | testdb | t | NULL | 6 | 6 | 0 | 6 | 0 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)
4. the count info is not updated in the following cases.
Attention please ,here I just list the situations using primary key,when using secondary key,it's the same result.
-- Range query using primary
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 12 | 6 | 6 | 6 | 6 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
mysql> explain select * from t where id<=3;
+----+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | p20240528,p20240529,p20240530,p20240531,p20240601,p20240602,p20240603,p20240604,p20240605,p20240606,p20240607,p20240608,p20240609,p20240610,p20240611,p20240612,p20240613,p20240614,p20240615,p20240616,p20240617,p20240618,pMax | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from t where id<=3;
+----+------+------+---------------------+
| id | col1 | col2 | partition_key |
+----+------+------+---------------------+
| 1 | 1 | abc | 2024-06-12 00:00:00 |
| 2 | 2 | abc | 2024-06-12 00:00:00 |
| 3 | 3 | abc | 2024-06-12 00:00:00 |
+----+------+------+---------------------+
3 rows in set (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 12 | 6 | 6 | 6 | 6 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.01 sec)
-- IN predicate using primary,the count info is not updated
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 12 | 6 | 6 | 6 | 6 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.01 sec)
mysql> select * from t where id in (1,2);
+----+------+------+---------------------+
| id | col1 | col2 | partition_key |
+----+------+------+---------------------+
| 1 | 1 | abc | 2024-06-12 00:00:00 |
| 2 | 2 | abc | 2024-06-12 00:00:00 |
+----+------+------+---------------------+
2 rows in set (0.00 sec)
mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb';
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| TABLE | testdb | t | 12 | 6 | 6 | 6 | 6 | 0 | 0 |
+-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
1 row in set (0.01 sec)
Description: First of all,thank you for taking the time to loot at my question. The following is my problem. When a table is created as partition table,the count info such as count_read,count_star in table_io_waits_summary_by_table and table_io_waits_summary_by_index_usage can't update as expected in some cases. As I tested,the follow cases will not update count info in PS io wait summary table: 1. range query using secondary index or primary ,include where condition is col_name in ('a','b') that in more than one value While the follow situations ,the count info is updated correctly: 1. DML SQL 2. equal conditions using secondary index or primary key 3. sql no using index Here,I list my test sql, the details can be seen in "How to repeat": Observe the count info: select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb'; SQL: 1. cases updatting count info correctly not as expected Range query using primary or secondary index： select * from t where id<=3; select * from t where id in (1,2); 2. cases updating count info correctly equal condition: select * from t where id=1; full table scan: SELECT * FROM t; How to repeat: 1. my test table structure CREATE TABLE `t` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `col1` int DEFAULT NULL, `col2` varchar(20) DEFAULT NULL, `partition_key` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`,`partition_key`), KEY `idx_col2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (to_days(`partition_key`)) (PARTITION p20240528 VALUES LESS THAN (739400) ENGINE = InnoDB, PARTITION p20240529 VALUES LESS THAN (739401) ENGINE = InnoDB, PARTITION p20240530 VALUES LESS THAN (739402) ENGINE = InnoDB, PARTITION p20240531 VALUES LESS THAN (739403) ENGINE = InnoDB, PARTITION p20240601 VALUES LESS THAN (739404) ENGINE = InnoDB, PARTITION p20240602 VALUES LESS THAN (739405) ENGINE = InnoDB, PARTITION p20240603 VALUES LESS THAN (739406) ENGINE = InnoDB, PARTITION p20240604 VALUES LESS THAN (739407) ENGINE = InnoDB, PARTITION p20240605 VALUES LESS THAN (739408) ENGINE = InnoDB, PARTITION p20240606 VALUES LESS THAN (739409) ENGINE = InnoDB, PARTITION p20240607 VALUES LESS THAN (739410) ENGINE = InnoDB, PARTITION p20240608 VALUES LESS THAN (739411) ENGINE = InnoDB, PARTITION p20240609 VALUES LESS THAN (739412) ENGINE = InnoDB, PARTITION p20240610 VALUES LESS THAN (739413) ENGINE = InnoDB, PARTITION p20240611 VALUES LESS THAN (739414) ENGINE = InnoDB, PARTITION p20240612 VALUES LESS THAN (739415) ENGINE = InnoDB, PARTITION p20240613 VALUES LESS THAN (739416) ENGINE = InnoDB, PARTITION p20240614 VALUES LESS THAN (739417) ENGINE = InnoDB, PARTITION p20240615 VALUES LESS THAN (739418) ENGINE = InnoDB, PARTITION p20240616 VALUES LESS THAN (739419) ENGINE = InnoDB, PARTITION p20240617 VALUES LESS THAN (739420) ENGINE = InnoDB, PARTITION p20240618 VALUES LESS THAN (739421) ENGINE = InnoDB, PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) 2.insert datas -- this step the count info is updated correctly mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | TABLE | testdb | t | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 2 rows in set (0.01 sec) mysql> mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.01 sec) mysql> insert into t values(null,1,'abc','2024-06-12'),(null,2,'abc','2024-06-12'),(null,3,'abc','2024-06-12'),(null,1,'bcd','2024-06-11'),(null,2,'bcd','2024-06-11'),(null,2,'bcd','2024-06-10'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | PRIMARY | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | TABLE | testdb | t | NULL | 6 | 0 | 6 | 0 | 6 | 0 | 0 | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 3 rows in set (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 6 | 0 | 6 | 0 | 6 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.00 sec) 3. the count info is updated in the following cases. Please attention,here I just list two cases: a. range query using primary key,when using secondary key,it's the same result.But I don't display here when using secondary key. b. full table scan mysql> TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage; Query OK, 0 rows affected (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | PRIMARY | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | TABLE | testdb | t | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 3 rows in set (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.00 sec) mysql> select * from t where id=1; +----+------+------+---------------------+ | id | col1 | col2 | partition_key | +----+------+------+---------------------+ | 1 | 1 | abc | 2024-06-12 00:00:00 | +----+------+------+---------------------+ 1 row in set (0.00 sec) -- the count_star increased by 2,not 1 mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 2 | 2 | 0 | 2 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | PRIMARY | 2 | 2 | 0 | 2 | 0 | 0 | 0 | | TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | TABLE | testdb | t | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 3 rows in set (0.00 sec) -- table scan : the count_star increased by total rows number in user table mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 2 | 2 | 0 | 2 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | PRIMARY | 2 | 2 | 0 | 2 | 0 | 0 | 0 | | TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | TABLE | testdb | t | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t; +----+------+------+---------------------+ | id | col1 | col2 | partition_key | +----+------+------+---------------------+ | 6 | 2 | bcd | 2024-06-10 00:00:00 | | 4 | 1 | bcd | 2024-06-11 00:00:00 | | 5 | 2 | bcd | 2024-06-11 00:00:00 | | 1 | 1 | abc | 2024-06-12 00:00:00 | | 2 | 2 | abc | 2024-06-12 00:00:00 | | 3 | 3 | abc | 2024-06-12 00:00:00 | +----+------+------+---------------------+ 6 rows in set (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 8 | 8 | 0 | 8 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | PRIMARY | 2 | 2 | 0 | 2 | 0 | 0 | 0 | | TABLE | testdb | t | idx_col2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | TABLE | testdb | t | NULL | 6 | 6 | 0 | 6 | 0 | 0 | 0 | +-------------+---------------+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 3 rows in set (0.00 sec) 4. the count info is not updated in the following cases. Attention please ,here I just list the situations using primary key,when using secondary key,it's the same result. -- Range query using primary mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 12 | 6 | 6 | 6 | 6 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.00 sec) mysql> explain select * from t where id<=3; +----+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | p20240528,p20240529,p20240530,p20240531,p20240601,p20240602,p20240603,p20240604,p20240605,p20240606,p20240607,p20240608,p20240609,p20240610,p20240611,p20240612,p20240613,p20240614,p20240615,p20240616,p20240617,p20240618,pMax | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where | +----+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from t where id<=3; +----+------+------+---------------------+ | id | col1 | col2 | partition_key | +----+------+------+---------------------+ | 1 | 1 | abc | 2024-06-12 00:00:00 | | 2 | 2 | abc | 2024-06-12 00:00:00 | | 3 | 3 | abc | 2024-06-12 00:00:00 | +----+------+------+---------------------+ 3 rows in set (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 12 | 6 | 6 | 6 | 6 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.01 sec) -- IN predicate using primary,the count info is not updated mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 12 | 6 | 6 | 6 | 6 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.01 sec) mysql> select * from t where id in (1,2); +----+------+------+---------------------+ | id | col1 | col2 | partition_key | +----+------+------+---------------------+ | 1 | 1 | abc | 2024-06-12 00:00:00 | | 2 | 2 | abc | 2024-06-12 00:00:00 | +----+------+------+---------------------+ 2 rows in set (0.00 sec) mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ | TABLE | testdb | t | 12 | 6 | 6 | 6 | 6 | 0 | 0 | +-------------+---------------+-------------+------------+------------+-------------+-------------+--------------+--------------+--------------+ 1 row in set (0.01 sec)