| Bug #116355 | optimizer trace missing in test_if_cheaper_ordering() | ||
|---|---|---|---|
| Submitted: | 15 Oct 2024 3:23 | Modified: | 15 Oct 2024 5:24 |
| Reporter: | tianfeng li (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Tracing | Severity: | S3 (Non-critical) |
| Version: | 8.0.30,8.4.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, optimizer_trace | ||
[15 Oct 2024 3:24]
tianfeng li
show more detailed optimizer trace information in test_if_cheaper_ordering() (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-show-more-detailed-optimizer-trace-information-in-te.patch (application/octet-stream, text), 2.33 KiB.
[15 Oct 2024 5:24]
MySQL Verification Team
Hello tianfeng li, Thank you for the report and contribution. regards, Umesh
Description: Hi, When I attempt to decipher a problem, I wonder why the optimizer selects an unreasonable index in the clause "reconsidering_access_paths_for_index_ordering." The only index I can observe within its nested "rows_estimation" is the idx_bad. However, where are the other indexes that might be more suitable? It appears that they are all concealed within a cost-based index selection function called test_if_cheaper_ordering(). ``` "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ { "rows_estimation": { "table": "`t_func_config`", "index": "idx_bad", "range_analysis": { ... } /* range_analysis */ } /* rows_estimation */ } ] /* steps */, "index_order_summary": {... } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ ``` How to repeat: as description above. Suggested fix: please consider showing more detailed cost based decision in test_if_cheaper_ordering().