Extremely slow performance on specific to Wordpress/Woocommerce joins

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