Description:
We have found that in some cases, the execution plan shown by explain is different from the actual execution plan, which can be seen through the optimizer trace. The join order of the explain and select statements is different.
A detailed analysis of the reasons reveals that the calculate_condition_filter() function handles EXPLAIN and SELECT statements differently. For the last table in a join, if it is not an EXPLAIN statement, it does not calculate condition_filter_pct in detail and directly sets it to 100, which ultimately leads to inconsistent plans.
How to repeat:
I was unable to reproduce the inconsistency between the EXPLAIN plan and the actual execution using simple data, but it can be seen that the cost shown in EXPLAIN differs from that of the actual execution.
CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
KEY `i_ab` (`a`,`b`)
);
CREATE TABLE `t2` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
KEY `i_ab` (`a`,`b`)
);
insert into t1 values (1,1),(2,2),(3,3);
insert into t2 values (1,1),(2,2),(3,3);
analyze table t1,t2;
select * from t2,t1 where t1.a>1 and t2.b<3 and t1.b> 1 and t1.a=t2.a order by t1.a;
explain format=tree
select * from t2,t1 where t1.a>1 and t2.b<3 and t1.b> 1 and t1.a=t2.a order by t1.a;
There is a difference between the optimizer traces of EXPLAIN and SELECT statements.
--- ./select.trace 2025-11-05 14:58:34.824005078 +0800
+++ ./explain.trace 2025-11-05 14:58:58.387106025 +0800
@@ -275,11 +275,11 @@
}
]
},
- "condition_filtering_pct": 100,
- "rows_for_plan": 0.666667,
+ "condition_filtering_pct": 33.3333,
+ "rows_for_plan": 0.222222,
"cost_for_plan": 0.893821,
- "sort_cost": 0.666667,
- "new_cost_for_plan": 1.56049,
+ "sort_cost": 0.222222,
+ "new_cost_for_plan": 1.11604,
"chosen": true
}
]
Suggested fix:
Make the behaviors of EXPLAIN and SELECT consistent.
Description: We have found that in some cases, the execution plan shown by explain is different from the actual execution plan, which can be seen through the optimizer trace. The join order of the explain and select statements is different. A detailed analysis of the reasons reveals that the calculate_condition_filter() function handles EXPLAIN and SELECT statements differently. For the last table in a join, if it is not an EXPLAIN statement, it does not calculate condition_filter_pct in detail and directly sets it to 100, which ultimately leads to inconsistent plans. How to repeat: I was unable to reproduce the inconsistency between the EXPLAIN plan and the actual execution using simple data, but it can be seen that the cost shown in EXPLAIN differs from that of the actual execution. CREATE TABLE `t1` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, KEY `i_ab` (`a`,`b`) ); CREATE TABLE `t2` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, KEY `i_ab` (`a`,`b`) ); insert into t1 values (1,1),(2,2),(3,3); insert into t2 values (1,1),(2,2),(3,3); analyze table t1,t2; select * from t2,t1 where t1.a>1 and t2.b<3 and t1.b> 1 and t1.a=t2.a order by t1.a; explain format=tree select * from t2,t1 where t1.a>1 and t2.b<3 and t1.b> 1 and t1.a=t2.a order by t1.a; There is a difference between the optimizer traces of EXPLAIN and SELECT statements. --- ./select.trace 2025-11-05 14:58:34.824005078 +0800 +++ ./explain.trace 2025-11-05 14:58:58.387106025 +0800 @@ -275,11 +275,11 @@ } ] }, - "condition_filtering_pct": 100, - "rows_for_plan": 0.666667, + "condition_filtering_pct": 33.3333, + "rows_for_plan": 0.222222, "cost_for_plan": 0.893821, - "sort_cost": 0.666667, - "new_cost_for_plan": 1.56049, + "sort_cost": 0.222222, + "new_cost_for_plan": 1.11604, "chosen": true } ] Suggested fix: Make the behaviors of EXPLAIN and SELECT consistent.