Re rewriting the query to UNION, I a gree this avoids the OR-ed equi-join problem, too.

• It does add some overhead as big parts of the SELECT are duplicated.
• But for each UNION part, the optimizer gets more freedom with join orders.
• One can imagine an optimizer that does such rewrite internally but not the MariaDB (or MySQL) optimizer. such transformation would be hard to implement.

...

Oxford Metadata wrote:

But when you add the second left join... then it is a different ballgame.
We are back to the 80 seconds.

Confirm. That's because optimizer_use_condition_selectivity problem is hit by a table inside the second outer join. One can see the problem where EXPLAIN shows type=index.

...

Oxford Metadata wrote

If in either LEFT JOIN we have not 1 term, but 3 or even 10 terms to look for, the impact in the timing is minimal.

Hmm I cannot reproduce this on my side. For me, the query with a lot of terms again suffers from optimizer_use_condition_selectivity=3 problem, which is fixed by setting it to 1. The number of terms do not matter much.

....

I tried to adapt the stored procedure with the UNION logic. Obviously I needed to create two more temporary tables to host the same array for the second part of the UNION.
Well, all benefits in performance had disappeared.
...

CREATE TEMPORARY TABLE IF NOT EXISTS aieo_temp_color_tax (object_id INT,

name VARCHAR(255) COLLATE utf8mb4_unicode_ci

)

There are two reasons for this:

• when creating temporary tables, you're "materializing" the join

  wp_term_relationships JOIN wp_term_taxonomy ON ... JOIN wp_terms ON ...

  Does the main query access a big fraction of record combinations from here? If not, you will be better off copy-pasting
  this code into both parts of the UNION.

• If you still prefer to materialize, create the temporary table with an index on object_id column.

An MDEV entry for the optimizer_use_condition_selectivity!=1 slowdown is: MDEV-22537 (there are actually many duplicate MDEVs fo that problem . Fixed in 11.0 by MDEV-26974.

This may be loosely related to MDEV-32663, which was discovered due to the WordPress plugin Woocommerce. It is apparently using the column wp_options.option_value as a key/value store, often updating that column with huge values. This is bloating the data file as well as the InnoDB buffer pool, because BLOBs in InnoDB are copy-on-write. The old BLOB values would eventually be freed by the InnoDB purge subsystem.

It might make sense to collect some statistics on the InnoDB buffer pool and the history list length while the workload is running.

If the join is slow also on an idle server where all history has been purged, then this comment can be disregarded.

@Marko

You are quite correct it is a major pain point in most woocommerce installations; particularly those that have many plugins (even unactive) or plugins that their authors opt to even store their user instructions in the wp_options table. Others use them for storage of transient information etc. It is a mess.
Also most developers who use this as a convenient storage omit to program their options with autoload=no. So in most cases all these options are stored into memory.

Here are some statistics of our case study that has 150 plugins, 125 of them active. (Yes to have a decent shop with the functionalities that say most corporate shops one needs as many plugins).

Here are the stats.

{{# total_options
'5896'}}

{{# autoload_percentage
'26.34'}}

{{# max_length
794146}}

{{# average_length
843.4795}}

{{# stddev_length
13267.8577

via

SELECT STDDEV_SAMP(LENGTH(option_value)) AS stddev_length

FROM wp_options;

}}

{{# variance_length
176036047.8903

via

SELECT VAR_SAMP(LENGTH(option_value)) AS variance_length

FROM wp_options; 

}}

Top "performers"

{{ option_id, option_name, value_length
43485, permalink-manager-uris, 794146
814372, fs_api_cache, 397920
7113210, 3140cac73646264190f1c5c5e36eb2e4, 201717
7149916, edd_api_request_3140cac73646264190f1c5c5e36eb2e4, 169091
6727900, edd_api_request_4a1ab5370c7bfe8933f18fbfc247aa5e, 163665
6686831, 518bf5b07229778e903743976ebeb827, 138071
6686861, edd_api_request_518bf5b07229778e903743976ebeb827, 138071
6834699, csconsent-magic, 132516
6565607, edd_sl_73499b5e164c73711a5f2eb6801dfa83, 122433
7321163, edd_sl_d3272f70ee9793b12e030656abc292ae, 107214
7287981, edd_sl_9e28c49717631ba889cd3acc944f7257, 82631
7287982, edd_sl_9771e8aa8da92dc6356f37ed61bab12a, 78620
6746, edd_sl_cb4aea2b7793477c7dbe8bd8449be8ac, 77390
209513, rewrite_rules, 76973
236222, automatewoo_products_last_on_sale, 66852
7295050, wp-all-import-pro_85b8495febade7a221f67b0bca7d59d9, 52227
937901, wpsso_options, 49190
7109956, woobe_options_for_all, 47951
7108846, woobe_options_6291, 47836
187297, woobe_options_1, 47219
281669, megamenu_themes, 41649
100, wp_user_roles, 37731
7237747, edd_sl_edbbdffd63446338b1d63d82777e08d8, 37138
267692, woobe_options_7, 35983
7318636, gform_version_info, 35120
7236358, 23d5d8026fb67b9c5d0ab9ae394f86d2, 33847
7252599, edd_api_request_23d5d8026fb67b9c5d0ab9ae394f86d2, 33847
6981486, wp-all-export-pro_f3e07d8a5567b52eda97b45478a42948, 33380
7154269, fibofilters_filters, 33010
7025158, wp_backup_user_roles, 31551
7027154, theme_mods_femme-fatale-V2, 30776
281505, edd_sl_351df0740465bb1da255c6e22299c7db, 30316
7279913, wpai-woocommerce-add-on_080fdc82a3a678dab2d4f10c27ddeb79, 29474
7154272, fibofilters_indexer_last_build_logs_tmp, 28707
7321081, fibofilters_indexer_last_build_logs, 28615
4485, theme_mods_femmefatale, 28006
2593648, aioseo_link_assistant_options, 27862}}

Autoloads (mind you we have optimized this application and have set many to no)

{{ autoload, average_length, option_count
, 10.0000, 2
auto, 206.0824, 182
no, 688.1174, 3884
off, 6410.6215, 214
on, 605.3443, 61
yes, 550.0200, 1553}}

{{ length_range, count_in_range
0-100, 5126
101-500, 408
501-1000, 127
1001-5000, 120
5001-20000, 68
20000+, 47

Code for this is this in case someone want to change the size of the logarithmic bins

SELECT

    CONCAT(

        POW(10, FLOOR(LOG10(LENGTH(option_value)))), '-',

        POW(10, FLOOR(LOG10(LENGTH(option_value))) + 1) - 1

    ) AS size_range,

    COUNT(*) AS count_in_range

FROM wp_options

GROUP BY FLOOR(LOG10(LENGTH(option_value)))

ORDER BY POW(10, FLOOR(LOG10(LENGTH(option_value))));

}}

Just migrated the reference Wordpress/Woocommerce installation from its original version (the highest that Cloudflare currently supports) to another host with the version 11.4.4 Both hosts have nearly identical hardware (same gen of EPYC processors, memory, storage)

Most settings were identical. Performance wise 11.4.4 is about 10-15% faster than 10.6.20 for a benchmark of a set of complex data gatering/housekeeping/statistics/meta-statistics queries.

But we have noticed a substantial difference in Locks. We realised that the new server was set with innodb_deadlock_detect disabled.

I wonder if @psergei can confirm that the deadlock detect metrics do have an impact to the optimizer.