Description:
row_examined in slowlog is not correct when using index_merge
How to repeat:
1. prepare table and data
CREATE TABLE `test_merge` (
`id` int NOT NULL AUTO_INCREMENT,
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
`c3` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test_merge` VALUES (1,1,2,'a'),(2,1,3,'a'),(3,1,4,'a'),(4,1,5,'a'),(5,3,2,'a'),(6,4,2,'a'),(7,5,2,'a'),(8,6,2,'a'),(9,2,2,'a'),(10,2,3,'a'),(11,2,4,'a'),(12,2,5,'a'),(13,2,6,'a');
2. execute sql and observe slowlog comparing with performance_schema.table_io_waits_summary_by_index_usage
explain select * from test_merge where c1=1 and c2=2; -- Ensure Extra： Using intersect(idx_c1,idx_c2);
truncate table performance_schema.table_io_waits_summary_by_index_usage;
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_schema='test'
and object_name='test_merge'; -- count_xxx: 0
set long_query_time=0;
select * from test_merge where c1=1 and c2=2;
-- slow log：Rows_examined: 0 not correct
# Time: 2024-09-11T09:05:32.035131Z
# User@Host: root[root] @ xxx [xxx] Id: 2438
# Query_time: 0.000474 Lock_time: 0.000003 Rows_sent: 1 Rows_examined: 0 Execution_time: 0.000471
SET timestamp=1726045532;
select * from test_merge where c1=1 and c2=2;
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_schema='test'
and object_name='test_merge'; -- count_xxx increased
