Looking for root cause for the following problem:
A query which comes from a generated report used to take seconds and now takes 21 min (tested in house on MariaDB ES 10.6.5, in MySQL 5.6 it took 9.611s Customer is having the issue in 10.2.27 .)
The customer found that by setting the optimizer_search_depth=10 the amount of time is similar to original run times. They tested 11 through 15 as well. They noticed `optimizer_search_depth=10` performed identically to `optimizer_search_depth=0` and `optimizer_search_depth=7`. However, `optimizer_search_depth=11` started to show degraded performance. They saw that at `optimizer_search_depth=15`, there was no, discernible difference between that and the default `optimizer_search_depth=62`.
Below are the steps to reproduce the issue.
The files are included in CS0379791-March102022.tar.gz in the case.
1. Provision MariaDB enterprise 10.6.5-2 (customer had issue with 10.2 and 10.4 DB) using server.cnf.txt (modified version of customers xpo_testcase_my.cnf).
2. Connect to MariaDB instance.
3. create database discard;
4. use discard;
5. source xpo_full_testcase.sql;
6. source xpo_testcase_explain.sql.
Optimizer trace with default optimzer_search_depth takes 12 hours and we were unable to capture the data.
However, setting optimizer_search_depth to 3 allowed the capture data from an optimizer trace see optimizer_output_depth3 .
Relevant files are attached to case CS0379791 as CS0379791-March102022.tar.gz
1). optimizer_search_depth_scaling.png - graph of time is takes to run the query when setting optimizer_search_depth = [1-25]
2). server.cnf.txt - config file used by support when reproducing the issue
3). xpo_testcase_my.cnf - config file usde by customer to reproduce the issue
4). xpo_testcase_explain.sql - explain of the query in question
5). xpo_full_testcase.sql - data dump to reproduce the issue with
6). optimizer_output_depth3 - optimzer trace with optimizer_search_depth = 3
7). CS0379791_output/ - flamegraph output including perf output and more
8). explain_json.txt - Additionally the customer has attached output from explain json from customer
I am looking at this now.
What is unique with this query is:
24 different tables
7 using 'ref' access (with one expected row), one using 'ALL' and the 16 using eq_ref
This means that almost all combinations has the same cost, which means that the optimizer is going trough roughly 24! = 6.2E+23 combinations.
After discussing this with Sergei Petrunia, we come up with the following ideas to speed up the optimizer stage:
When searching for combinations, if we find a set of tables at the end with 'eq_ref', skip back to the first not eq_ref table and continue searching from there.
After going trough tables, remember the lowest possible cost for adding the table. When checking if we should cut of the search, add the minimum possible cost for the renaming tables to the current cost when comparing with the current best cost.
This should cut down the number of combinations notable.
Michael Widenius
added a comment - I am looking at this now.
What is unique with this query is:
24 different tables
7 using 'ref' access (with one expected row), one using 'ALL' and the 16 using eq_ref
This means that almost all combinations has the same cost, which means that the optimizer is going trough roughly 24! = 6.2E+23 combinations.
After discussing this with Sergei Petrunia, we come up with the following ideas to speed up the optimizer stage:
When searching for combinations, if we find a set of tables at the end with 'eq_ref', skip back to the first not eq_ref table and continue searching from there.
After going trough tables, remember the lowest possible cost for adding the table. When checking if we should cut of the search, add the minimum possible cost for the renaming tables to the current cost when comparing with the current best cost.
This should cut down the number of combinations notable.
A comment on the current patch:
All queries that has a several EQ_REF joins (joins on primary or unique keys which all key parts usable) should be faster. The more EQ_REF joins, the better performance when choosing plan.
I am now working on several new ideas of how to get down the combinations checked by the optimizer. Some of them will be in 10.6,
the others will be in 10.9 or 10.10. Will update this ticket with more information in 1-2 weeks
Michael Widenius
added a comment - A comment on the current patch:
All queries that has a several EQ_REF joins (joins on primary or unique keys which all key parts usable) should be faster. The more EQ_REF joins, the better performance when choosing plan.
I am now working on several new ideas of how to get down the combinations checked by the optimizer. Some of them will be in 10.6,
the others will be in 10.9 or 10.10. Will update this ticket with more information in 1-2 weeks
I am almost done with this task. The current work consist of:
Fixed an issue with table order getting scrambled. This allows the greedy_optimizer to find good plans faster in the
case that smaller tables should be used first (common case).
Improved table pruning in optimizer by updating the current key_dependency as part of calculating plans.
This allows us to prune some plans earlier that we couldn't do before (as we didn't know before if all keys where already usable)
Improve pruning in greedy_search by sorting tables during search
This allows us to find good plans faster, which allows us to skip more bad plans
Add eq_ref optimization (something similar as MySQL has but totally different approach). This chains EQ_REF tables (tables that can use an unique key to find a row) when doing optimization, which significantly reduces search combinations. This is optimization is enabled when setting
optimizer_prune_level=2 (which is now the new default).
The effect of all this is a significant improvement of the speed of greedy_optimizer when table pruning is enabled.
I did a quick comparison with MySQL 5.7 (which has eq_ref chaining optimization) by running greedy_search.test on the resulting code:
Test for optimized builds:
--optimizer_trace
Test for optimized builds:
mysql: main.greedy_optimizer 1956 ms
mariadb: main.greedy_optimizer prune_level=1 1773 ms
mariadb: main.greedy_optimizer prune_level=2 1369 ms
In MariaDB 10.6 default, the above tests takes a VERY long time.
Of the above work, the first 2 parts of the work are scheduled to be added to MariaDB 10.6 and the last works are scheduled for 10.10
You can find this work in bb-10.6-monty (until it is pushed to 10.6 and 10.10)
Michael Widenius
added a comment - - edited I am almost done with this task. The current work consist of:
Fixed an issue with table order getting scrambled. This allows the greedy_optimizer to find good plans faster in the
case that smaller tables should be used first (common case).
Improved table pruning in optimizer by updating the current key_dependency as part of calculating plans.
This allows us to prune some plans earlier that we couldn't do before (as we didn't know before if all keys where already usable)
Improve pruning in greedy_search by sorting tables during search
This allows us to find good plans faster, which allows us to skip more bad plans
Add eq_ref optimization (something similar as MySQL has but totally different approach). This chains EQ_REF tables (tables that can use an unique key to find a row) when doing optimization, which significantly reduces search combinations. This is optimization is enabled when setting
optimizer_prune_level=2 (which is now the new default).
The effect of all this is a significant improvement of the speed of greedy_optimizer when table pruning is enabled.
I did a quick comparison with MySQL 5.7 (which has eq_ref chaining optimization) by running greedy_search.test on the resulting code:
Test for optimized builds:
--optimizer_trace
Test for optimized builds:
mysql: main.greedy_optimizer 1956 ms
mariadb: main.greedy_optimizer prune_level=1 1773 ms
mariadb: main.greedy_optimizer prune_level=2 1369 ms
In MariaDB 10.6 default, the above tests takes a VERY long time.
Of the above work, the first 2 parts of the work are scheduled to be added to MariaDB 10.6 and the last works are scheduled for 10.10
You can find this work in bb-10.6-monty (until it is pushed to 10.6 and 10.10)
As far as I understand, it fixes some cases. Other cases will be fixed in 10.10, to avoid confusion they should get a new MDEV number.
monty, that case that rob.schwyzer@mariadb.com is talking about — was it supposed to be fixed in 10.6 or in 10.10?
Sergei Golubchik
added a comment - Commit https://github.com/MariaDB/server/commit/b729896d00e was pushed and released in 10.6.8.
As far as I understand, it fixes some cases. Other cases will be fixed in 10.10, to avoid confusion they should get a new MDEV number.
monty , that case that rob.schwyzer@mariadb.com is talking about — was it supposed to be fixed in 10.6 or in 10.10?
The code in bb-10.6-monty# was updated since we spoked last time with the changes that has already gone into 10.6 (which is just some small improvements to greedy_search() as list in my post above). This is why optimizer_prune_level could not be changed.
The big changes are in bb-10.10-monty, which is in QA testing and will be pushed to 10.10 as soon as QA gives a green light.
The state of the original test case:
In current 10.6 (that doesn't have optimizer_prune_level=2)
source xpo_testcase_explain.sql takes 0.092 second with default search_depth 62 and 0.032 seconds with search_depth 16
In bb-10.10-monty with default settings (prune_level=2, search_dept 62):
source xpo_testcase_explain.sql takes 0.026 seconds
Note that in 10.6, the main reason it is faster than before was the EQ_REF shortcuting that was done in April. This only solves special cases where there are a lot of EQ_REF tables. The code in 10.10 is more general and can solve a wider range of cases.
Michael Widenius
added a comment - The code in bb-10.6-monty# was updated since we spoked last time with the changes that has already gone into 10.6 (which is just some small improvements to greedy_search() as list in my post above). This is why optimizer_prune_level could not be changed.
The big changes are in bb-10.10-monty, which is in QA testing and will be pushed to 10.10 as soon as QA gives a green light.
The state of the original test case:
In current 10.6 (that doesn't have optimizer_prune_level=2)
source xpo_testcase_explain.sql takes 0.092 second with default search_depth 62 and 0.032 seconds with search_depth 16
In bb-10.10-monty with default settings (prune_level=2, search_dept 62):
source xpo_testcase_explain.sql takes 0.026 seconds
Note that in 10.6, the main reason it is faster than before was the EQ_REF shortcuting that was done in April. This only solves special cases where there are a lot of EQ_REF tables. The code in 10.10 is more general and can solve a wider range of cases.
Some optimizations related to the original test case were implemented in 10.6.8 (10.7.4, 10.8.3), some in 10.6.9 (10.7.5, 10.8.4).
But the issue was completely resolved only in 10.10.1 (MDEV-28852).
Sergei Golubchik
added a comment - Note
Some optimizations related to the original test case were implemented in 10.6.8 (10.7.4, 10.8.3), some in 10.6.9 (10.7.5, 10.8.4).
But the issue was completely resolved only in 10.10.1 ( MDEV-28852 ).
People
Michael Widenius
Maria M Pflaum (Inactive)
Votes:
1Vote for this issue
Watchers:
8Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":1564.1000003814697,"ttfb":630,"pageVisibility":"visible","entityId":108718,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"bc5a6ffe-37c2-47c5-8318-e0b65de2e524","navigationType":0,"readyForUser":1665.8000001907349,"redirectCount":0,"resourceLoadedEnd":2164.6000003814697,"resourceLoadedStart":635.4000000953674,"resourceTiming":[{"duration":249.2000002861023,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":635.4000000953674,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":635.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":884.6000003814697,"responseStart":0,"secureConnectionStart":0},{"duration":252.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":635.7000002861023,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":635.7000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":888.6000003814697,"responseStart":0,"secureConnectionStart":0},{"duration":349.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":635.8000001907349,"connectEnd":635.8000001907349,"connectStart":635.8000001907349,"domainLookupEnd":635.8000001907349,"domainLookupStart":635.8000001907349,"fetchStart":635.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":635.8000001907349,"responseEnd":985,"responseStart":985,"secureConnectionStart":635.8000001907349},{"duration":460.30000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":636,"connectEnd":636,"connectStart":636,"domainLookupEnd":636,"domainLookupStart":636,"fetchStart":636,"redirectEnd":0,"redirectStart":0,"requestStart":636,"responseEnd":1096.3000001907349,"responseStart":1096.3000001907349,"secureConnectionStart":636},{"duration":465.2999997138977,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":636.2000002861023,"connectEnd":636.2000002861023,"connectStart":636.2000002861023,"domainLookupEnd":636.2000002861023,"domainLookupStart":636.2000002861023,"fetchStart":636.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":636.2000002861023,"responseEnd":1101.5,"responseStart":1101.5,"secureConnectionStart":636.2000002861023},{"duration":466.2000002861023,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":636.4000000953674,"connectEnd":636.4000000953674,"connectStart":636.4000000953674,"domainLookupEnd":636.4000000953674,"domainLookupStart":636.4000000953674,"fetchStart":636.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":636.4000000953674,"responseEnd":1102.6000003814697,"responseStart":1102.6000003814697,"secureConnectionStart":636.4000000953674},{"duration":467.2999997138977,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":636.6000003814697,"connectEnd":636.6000003814697,"connectStart":636.6000003814697,"domainLookupEnd":636.6000003814697,"domainLookupStart":636.6000003814697,"fetchStart":636.6000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":636.6000003814697,"responseEnd":1103.9000000953674,"responseStart":1103.9000000953674,"secureConnectionStart":636.6000003814697},{"duration":552.8000001907349,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":636.8000001907349,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":636.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1189.6000003814697,"responseStart":0,"secureConnectionStart":0},{"duration":468.80000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":636.9000000953674,"connectEnd":636.9000000953674,"connectStart":636.9000000953674,"domainLookupEnd":636.9000000953674,"domainLookupStart":636.9000000953674,"fetchStart":636.9000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":636.9000000953674,"responseEnd":1105.7000002861023,"responseStart":1105.7000002861023,"secureConnectionStart":636.9000000953674},{"duration":552.9000000953674,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":637,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":637,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1189.9000000953674,"responseStart":0,"secureConnectionStart":0},{"duration":470.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":637.2000002861023,"connectEnd":637.2000002861023,"connectStart":637.2000002861023,"domainLookupEnd":637.2000002861023,"domainLookupStart":637.2000002861023,"fetchStart":637.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":637.2000002861023,"responseEnd":1107.4000000953674,"responseStart":1107.4000000953674,"secureConnectionStart":637.2000002861023},{"duration":1486.3000001907349,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":647.8000001907349,"connectEnd":647.8000001907349,"connectStart":647.8000001907349,"domainLookupEnd":647.8000001907349,"domainLookupStart":647.8000001907349,"fetchStart":647.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":647.8000001907349,"responseEnd":2134.1000003814697,"responseStart":2134.1000003814697,"secureConnectionStart":647.8000001907349},{"duration":1485.2999997138977,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":649.1000003814697,"connectEnd":649.1000003814697,"connectStart":649.1000003814697,"domainLookupEnd":649.1000003814697,"domainLookupStart":649.1000003814697,"fetchStart":649.1000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":649.1000003814697,"responseEnd":2134.4000000953674,"responseStart":2134.4000000953674,"secureConnectionStart":649.1000003814697},{"duration":554.5999999046326,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1210.8000001907349,"connectEnd":1210.8000001907349,"connectStart":1210.8000001907349,"domainLookupEnd":1210.8000001907349,"domainLookupStart":1210.8000001907349,"fetchStart":1210.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":1210.8000001907349,"responseEnd":1765.4000000953674,"responseStart":1765.4000000953674,"secureConnectionStart":1210.8000001907349},{"duration":597.8999996185303,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1539.1000003814697,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1539.1000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2137,"responseStart":0,"secureConnectionStart":0},{"duration":371,"initiatorType":"link","name":"https://jira.mariadb.org/s/d5715adaadd168a9002b108b2b039b50-CDN/lu2cib/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/css/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":1773,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1773,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2144,"responseStart":0,"secureConnectionStart":0},{"duration":365.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/e65b778d185daf5aee24936755b43da6/_/download/contextbatch/js/browser-metrics-plugin.contrib,-_super,-project.issue.navigator,-jira.view.issue,-atl.general/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":1774.3000001907349,"connectEnd":1774.3000001907349,"connectStart":1774.3000001907349,"domainLookupEnd":1774.3000001907349,"domainLookupStart":1774.3000001907349,"fetchStart":1774.3000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":1774.3000001907349,"responseEnd":2139.5,"responseStart":2139.5,"secureConnectionStart":1774.3000001907349},{"duration":389.80000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/097ae97cb8fbec7d6ea4bbb1f26955b9-CDN/lu2cib/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/js/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":1774.8000001907349,"connectEnd":1774.8000001907349,"connectStart":1774.8000001907349,"domainLookupEnd":1774.8000001907349,"domainLookupStart":1774.8000001907349,"fetchStart":1774.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":1774.8000001907349,"responseEnd":2164.6000003814697,"responseStart":2164.6000003814697,"secureConnectionStart":1774.8000001907349}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":459,"responseStart":630,"responseEnd":645,"domLoading":633,"domInteractive":2271,"domContentLoadedEventStart":2271,"domContentLoadedEventEnd":2343,"domComplete":3095,"loadEventStart":3095,"loadEventEnd":3096,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2230.7000002861023},{"name":"bigPipe.sidebar-id.end","time":2231.6000003814697},{"name":"bigPipe.activity-panel-pipe-id.start","time":2231.800000190735},{"name":"bigPipe.activity-panel-pipe-id.end","time":2234.1000003814697},{"name":"activityTabFullyLoaded","time":2374.800000190735}],"measures":[],"correlationId":"f7481d36a7ae94","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":114,"dbReadsTimeInMs":11,"dbConnsTimeInMs":19,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Critical MDEV-28852 Improve optimization of joins with many tables, including eq_ref tables
Page Failed to load
Page Loading...
I am looking at this now.
What is unique with this query is:
This means that almost all combinations has the same cost, which means that the optimizer is going trough roughly 24! = 6.2E+23 combinations.
After discussing this with Sergei Petrunia, we come up with the following ideas to speed up the optimizer stage:
This should cut down the number of combinations notable.