Bug #106944 can_switch_from_ref_to_range() doesn't work for prepare statement
Submitted: 7 Apr 2022 12:28 Modified: 9 May 2022 11:53
Reporter: Shuai Wang Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[7 Apr 2022 12:28] Shuai Wang
I noticed there is significant performance loss if I execute my statement in prepare mode. After examining the source code, I think there might be some problems in the can_switch_from_ref_to_range method.

In the method description, "ref access depends on a constant, not a value read from a table earlier in the join sequence." this is condition 4 for doing switch.

In sql_optimizer.cc:2735, here we use dep_map to determine if the key value is a constant(true if dep_map is zero). However, if we execute our sql statement in prepare mode, keyuse->val->used_tables() would give us INNER_TABLE_BIT, which is not zero. But it's still a constant, according to the comment "Parameter is treated as constant during execution, thus it will not be evaluated during preparation." Therefore, prepare doesn't get optimized as the normal sql do.

How to repeat:
Please refer to the source code

Suggested fix:
+ sql_select:2085 *dep_map &= ~INNER_TABLE_BIT
(happy to help if it is accepted)
[7 Apr 2022 13:33] MySQL Verification Team
Hi Mr. Wang,

Thank you for your bug report.

If we understood you correctly, MySQL Optimiser has one execution plan for the prepared statement and another for the normal statement.

We would very much like to check that.

Please, send us the fully repeatable test case. It has to contain all the tables involved (both CREATE and all rows) and exact query , with a manner of execution in SQL and not in API, followed by EXPLAINs of both plans.

We are waiting on your feedback.
[8 Apr 2022 8:05] Shuai Wang

Yes, you got it right.

I might need some extra time to create the test case. Our dataset is quite large and I don't think I'm allowed to provide it. Sorry about that.

And I don't know how to find the execution plan for the prepare statement. I use gdb and slow sql to find that prepared statement do scan more rows than the normal sql do. Please tell me if you have better way to do the analysis.

I'll update it once I have the test case.

[8 Apr 2022 12:03] MySQL Verification Team
Hi Mr. Wang,

We are eagerly waiting on your feedback.
[9 May 2022 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[9 May 2022 11:53] MySQL Verification Team
Hi Mr. Wang,

We can not repeat your problem without the proper test case.

Can't repeat.