- Issue created by @solideogloria
- Status changed to Closed: outdated
8 months ago 8:09pm 26 March 2024 The fix is to set
optimizer_search_depth=0in my.cnfFor DDEV, create
.ddev/mysql/my.cnfwith[server] optimizer_search_depth=0
Using a reverse webform submission relationship results in extremely slow query execution on MariaDB < 10.5. I don't know if this specifically can be fixed, but I figured it was worth reporting, as it may help others running into the issue.
While using the relationship isn't noticeably slow with small amounts of submissions (like 5 for each webform), when there are hundreds or thousands of submissions, the join is very slow, even when using a paged query returning only a few results per page.
Not only that, but when running the resulting SQL directly on the DB from a SQL shell, the query generates lots of warnings like:
mariadb truncated incorrect DOUBLE value ''
mariadb truncated incorrect INTEGER value ''
"{})drush sql:cli or some other method, run the SQL on the DB.SHOW WARNINGS;The warnings occur due to the ordering of JOIN conditions. The generated SQL for the reverse relationship is like this:
LEFT JOIN webform_submission_data webform_submission_data
ON webform_submission.sid = webform_submission_data.value
AND webform_submission_data.webform_id = 'webform_2'
AND webform_submission_data.name = 'related_webform_1'
LEFT JOIN webform_submission webform_views_entity_reverse_webform_submission
ON webform_submission_data.sid = webform_views_entity_reverse_webform_submission.sid
This is a problem because of ON webform_submission.sid = webform_submission_data.value. The sid is an integer. However, webform_submission_data.value is not always, at least not until you limit the submissions by webform_id and name. JOIN conditions are applied in order, so your string submission values (such as "TEST STRING" listed in the steps above) are joined to sid, resulting in lots and lots of warnings.
If you manually modify the SQL to be like this:
LEFT JOIN webform_submission_data webform_submission_data
ON webform_submission_data.webform_id = 'webform_2'
AND webform_submission_data.name = 'related_webform_1'
AND webform_submission.sid = webform_submission_data.value
LEFT JOIN webform_submission webform_views_entity_reverse_webform_submission
ON webform_submission_data.sid = webform_views_entity_reverse_webform_submission.sid
There will be no warnings, because by the time that webform_submission.sid = webform_submission_data.value is checked, the values are only the correct ones.
However, it should be noted that even after "correcting" the SQL to remove all warnings, the performance will not be any different.
Regarding the warnings, I'm not sure if it's possible to create this SQL with code in WebformViewsEntityReverse::query(), or if you'd need to alter the query in a hook to get this.
As a solution to the performance issue, the user can upgrade the database to MariaDB >= 10.5, as the performance was improved. When running a query on 10.3 with a few thousand submissions of each webform, it ran in ~45 seconds, while running on 10.5 it ran in ~4.8 seconds. So it runs about 9 or 10 times faster.
Another small performance gain can be obtained by using a Mini Pager instead of a Full Pager.
Closed: outdated
5.0
Code
It affects performance. It is often combined with the Needs profiling tag.
The fix is to set optimizer_search_depth=0 in my.cnf
For DDEV, create .ddev/mysql/my.cnf with
[server]
optimizer_search_depth=0