| Bug #113699 | QEP regression with prefer_ordering_index=off | ||
|---|---|---|---|
| Submitted: | 21 Jan 2024 16:58 | Modified: | 7 Aug 2024 17:50 |
| Reporter: | Daniel Nichter (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 8.0, 8.1, 8.2, 8.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[22 Jan 2024 21:05]
Daniel Nichter
Typo in “How to repeat”: the first, mysql> SET optimizer_switch = "prefer_ordering_index=off"; should be mysql> SET optimizer_switch = "prefer_ordering_index=on"; Copy-paste error.
[23 Jan 2024 7:10]
MySQL Verification Team
Hello Daniel, Thank you for the report and feedback. regards, Umesh
[23 Jan 2024 18:50]
Daniel Nichter
Patch 8.0.35 sql/sql_optimizer.cc to fix issue (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: sql_optimizer.cc.patch (application/octet-stream, text), 957 bytes.
[23 Jan 2024 18:51]
Daniel Nichter
Patch 8.0.35 mysql-test/t/order_by_limit.test to test issue and fix (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: order_by_limit.test.patch (application/octet-stream, text), 908 bytes.
[23 Jan 2024 18:52]
Daniel Nichter
Patch 8.0.35 mysql-test/r/order_by_limit.result to test issue and fix (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: order_by_limit.result.patch (application/octet-stream, text), 1.07 KiB.
[24 Jan 2024 5:13]
MySQL Verification Team
Thank you for the Contribution. Sincerely, Umesh
[3 Apr 2024 22:12]
Sanket Kanjalkar
Do have a timeline for this fix? I am facing a similar issue and would love see this in new release.
[6 Aug 2024 15:55]
Jon Stephens
Documented fix as follows in the MySQL 8.0.40, 8.4.3, and 9.1.0 changelogs:
With prefer_ordering_index=off, queries without a reference key
("ref key") regressed to scanning and sorting the full table
even though it should have been possible to avoid the sort.
Our thanks to Daniel Nichter for the contribution.
Closed.
[7 Aug 2024 17:50]
Daniel Nichter
Happy to see this fix released, thank you!
Description: With prefer_ordering_index=off, queries without a reference key ("ref key") regress to scanning and sorting the full table even though the sort can be avoided. When combined with a LIMIT clause, the regression can make the query even slower because it can't scan in order and return early. prefer_ordering_index is meant to control if MySQL chooses an ordering index rather than a non-ordering index. (Even when enabled, MySQL chooses an ordering index only if it costs less than non-ordering: "index_scan_time < read_time" in test_if_cheaper_ordering().) The regression occurs when there's no ref key. In this case, prefer_ordering_index should not apply because there's no choice, no preference. But currently the flag does apply, which means certain queries regress to the most expensive plan even though it can (and should) be avoided. The provided patch calls test_if_cheaper_ordering() if prefer_ordering_index=off _AND_ there's no ref key. Currently it does this by checking if "tab->type() == JT_ALL", but maybe "ref_key == -1" would be better? The result is a far better QEP for certain queries and no changes to other queries. An MTR test case/patch is provided. All original tests still pass. How to repeat: Load Jeremy's orignal test case: https://bugs.mysql.com/bug.php?id=97001 -- -- Normal (default) -- mysql> SET optimizer_switch = "prefer_ordering_index=off"; mysql> EXPLAIN SELECT * FROM t ORDER BY id LIMIT 1; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | index | NULL | PRIMARY | 8 | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ 1 row in set, 1 warning (22.04 sec) -- -- Regression -- mysql> SET optimizer_switch = "prefer_ordering_index=off"; mysql> EXPLAIN SELECT * FROM t ORDER BY id LIMIT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 998656 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ Even with prefer_ordering_index=off, there's no choice between indexes, so MySQL should use the only possible index to avoid the filesort. Suggested fix: Attached patches were made on 8.0.35, but they're small so they should apply to all current versions.