Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.4.33, 10.6.17, 11.4.1
-
Debian 11 Linux, Windows 11, Ubuntu 20.4 Linux
Description
For a rather popular query in determining the product attributes in woocommerce we noted a serious performance issue in MariaDB 10.4 and 10.6 (no other versions were tested, thus this issue might be present in other versions).
In MariaDB the performance of the same query (in the same dataset) can be up to 100 times slower than the same query in MySQL 5.7/8.0.36.
The culprit is when we have joins of this form:
LEFT JOIN |
(SELECT trs.object_id, ts.name FROM wp_term_relationships trs |
JOIN wp_term_taxonomy xs ON xs.term_taxonomy_id = trs.term_taxonomy_id |
JOIN wp_terms ts ON ts.term_id = xs.term_id |
WHERE xs.taxonomy IN ('pa_size', 'pa_megethos', 'pa_noumero')) AS ts ON p.id = ts.object_id |
LEFT JOIN |
(SELECT trc.object_id, tc.name FROM wp_term_relationships trc |
JOIN wp_term_taxonomy xc ON xc.term_taxonomy_id = trc.term_taxonomy_id |
JOIN wp_terms tc ON tc.term_id = xc.term_id |
WHERE xc.taxonomy IN ('pa_color', 'pa_chroma', 'pa_colour')) AS tc ON p.id = tc.object_id}} |
We feel that either a tuning parameter needs to be set (to which we are unaware) or there is a major flaw/bug in the core of MariaDB.
Similar issues have been observed in MS SQL Server. However, in this particular instance MS SQL is on par with MySQL 5.7 performance.
As this query is valuable in woocommerce installations it will be great if a fix is to be issued and/or guidelines to sort out the issue as it impacts millions of installations.
Please see our thread there:
https://github.com/woocommerce/woocommerce/issues/46699#issuecomment-2093998359
A sample dataset is provided here: https://github.com/woocommerce/woocommerce/files/15212736/BenchmarkDump.zip
(the scripts can be found here:
https://github.com/woocommerce/woocommerce/files/15213472/sql-scripts.zip
Attachments
Issue Links
- relates to
-
MDEV-34172 Join optimizer: fanout of LEFT JOIN cannot be less than one
-
- Open
-