MySQL Bugs: #106944: can_switch_from_ref_to_range() doesn't work for prepare statement

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:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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)

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.

Hi,

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.

Regards,
Shuai

Hi Mr. Wang,

We are eagerly waiting on your feedback.

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".

Hi Mr. Wang,

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

Can't repeat.