| Bug #112737 | Conditions refering to const table were not added to RANGE SCAN | ||
|---|---|---|---|
| Submitted: | 16 Oct 2023 9:07 | Modified: | 22 Apr 20:11 |
| Reporter: | Xingyu Yang (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 8.0, 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[16 Oct 2023 9:23]
Xingyu Yang
The const table map was obtained through qep_tab in test_quick_select(). But bug #32976857 removed the qep_tab in test_quick_select(), and did not pass the const table map to test_quick_select(). Here is the commit message of Bug #32976857 Bug #32976857: REMOVE QEP_TAB_STANDALONE [range optimizer, noclose] ``` Remove the QEP_TAB dependency from test_quick_select() (ie., the range optimizer). Change-Id: Ie0fcce71dfc813920711c43c3d62635dae0d7d20 ```
[16 Oct 2023 9:31]
MySQL Verification Team
Hello Xingyu Yang, Thank you for the report and contribution. Please ensure to upload patch via "Contribution" tab. Thank you. regards, Umesh
[16 Oct 2023 9:33]
Xingyu Yang
bugfix based on version 8.0.30 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-bugfix-issue-594-Index-conditions-referencing-the-co.patch (application/octet-stream, text), 17.87 KiB.
[23 Apr 2025 16:11]
Marc Reilly
Hi Umesh, Any chance the contribution to fix this regression could get merged in the next minor? It is still affecting the latest minor on 8.0. There is also a detailed write up on it here: https://github.com/enhancedformysql/blogs/blob/main/performance_degradation.md Thanks, Marc
[24 Mar 9:05]
Chaithra Marsur Gopala Reddy
Hi Xingyu Yang, Thank you for your contribution to MySQL – we’ve merged your change and it will be part of the upcoming release. We really appreciate the time and effort you put into this. Thanks again for your help and for being part of the community. Best Regards, MySQL team
[22 Apr 20:11]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 8.0.46, 8.4.9, and 9.7.0 release notes: Conditions on const tables and on previously joined tables were not consistently taken into account when building ranges in get_quick_record_count(). This could lead to suboptimal or incorrect range estimates, especially when range predicates depended on values from const or earlier-joined tables. Our thanks to Xingyu Yang for the contribution.
Description: Index conditions referring to const table should be added to RANGE SCAN as the condition value can be read before execution. Bug#32976857 omitted to pass the const table map when judging the condition-related table, resulting in the index condition referencing the const table not being added to the range scan, and the range scan needed to scan more data. How to repeat: CREATE TABLE t1 (a INTEGER, b INTEGER, PRIMARY KEY (a)); CREATE TABLE t2 (a INTEGER, b INTEGER, PRIMARY KEY (a, b)); INSERT INTO t1 VALUES (1, 1), (2, 2); INSERT INTO t2 VALUES (2, 1), (2, 2), (2, 3), (2, 4); ANALYZE TABLE t1, t2; EXPLAIN FORMAT=TREE SELECT t1.b FROM t1 JOIN t2 WHERE t1.a = t2.a AND t2.a = 2 AND t2.b >= t1.b AND t2.b <= t1.b+2; -- b<=4 was not added to RANGE SCAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: ((t2.a = 2) and (t2.b >= '2') and (t2.b <= <cache>(('2' + 2)))) (cost=0.86 rows=3) -> Covering index range scan on t2 using PRIMARY over (a = 2 AND 2 <= b) (cost=0.86 rows=3) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Suggested fix: Fix it by taking the const table map into account when adding conditions to RANGE SCAN.