Terrible performance on WP-generated query migrating from MySQL to MariaDB

Question

I'm trying to migrate a website from a WP-focused hosting provider that uses Percona for their DB node to a Jelastic-based provider that offers MariaDB for its DB nodes.

There's one query in particular (though not the only one) that runs significantly slower.

explain SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM   wp_posts
       INNER JOIN wp_postmeta
               ON ( wp_posts.id = wp_postmeta.post_id )
       INNER JOIN wp_postmeta AS mt1
               ON ( wp_posts.id = mt1.post_id )
WHERE  1 = 1
       AND ( wp_postmeta.meta_key = '_llms_order'
             AND (( mt1.meta_key = '_llms_parent_section'
                    AND mt1.meta_value =247476 )) )
       AND (( wp_posts.post_type = 'lesson'
              AND ( wp_posts.post_status = 'publish'
                     OR wp_posts.post_status = 'expired'
                     OR wp_posts.post_status = 'acf-disabled'
                     OR wp_posts.post_status = 'llms-completed'
                     OR wp_posts.post_status = 'llms-active'
                     OR wp_posts.post_status = 'llms-expired'
                     OR wp_posts.post_status = 'llms-on-hold'
                     OR wp_posts.post_status = 'llms-pending-cancel'
                     OR wp_posts.post_status = 'llms-pending'
                     OR wp_posts.post_status = 'llms-cancelled'
                     OR wp_posts.post_status = 'llms-refunded'
                     OR wp_posts.post_status = 'llms-failed'
                     OR wp_posts.post_status = 'llms-txn-failed'
                     OR wp_posts.post_status = 'llms-txn-pending'
                     OR wp_posts.post_status = 'llms-txn-refunded'
                     OR wp_posts.post_status = 'llms-txn-succeeded'
                     OR wp_posts.post_status = 'tribe-ea-success'
                     OR wp_posts.post_status = 'tribe-ea-failed'
                     OR wp_posts.post_status = 'tribe-ea-schedule'
                     OR wp_posts.post_status = 'tribe-ea-pending'
                     OR wp_posts.post_status = 'tribe-ea-draft'
                     OR wp_posts.post_status = 'private' ) ))
GROUP  BY wp_posts.id
ORDER  BY wp_postmeta.meta_value + 0 ASC
LIMIT  0, 999999 

This query is generated by WordPress. It gets all lessons within a specific section, ordering them by another postmeta value.

On Percona, it runs in 0.05 seconds. On Maria, it runs in 1.5 seconds (!!!).

MariaDB EXPLAIN results:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra	FIELD11
1	SIMPLE	wp_posts	ref	PRIMARY	type_status_date	type_status_date	82	const	31655	Using index condition; Using where; Using temporary; Using filesort
1	SIMPLE	mt1	ref	post_id	meta_key	post_id	8	dbname.wp_posts.ID	15	Using where
1	SIMPLE	wp_postmeta	ref	post_id	meta_key	post_id	8	dbname.wp_posts.ID	15	Using where

Mysql EXPLAIN results:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra	FIELD13	FIELD14	FIELD15	FIELD16
1	SIMPLE	mt1	NULL	ref	post_id	meta_key	meta_key	767	const	33378	10.00	Using where; Using temporary; Using filesort
1	SIMPLE	wp_posts	NULL	eq_ref	PRIMARY	type_status_date	post_parent	post_author	post_name	PRIMARY	8	dbname.mt1.post_id	1	26.35	Using where
1	SIMPLE	wp_postmeta	NULL	ref	post_id	meta_key	post_id	8	dbname.mt1.post_id	23	1.95	Using where

The postmeta table is indexed on post_id and meta_key. The posts table has a multi-column index on post_type, post_status, post_date and ID.

I don't think the OS (or DB optimization values!) have any bearing whatsoever here. I've tested the same with MariaDB on the old host, MySQL on the new one, even local installations of both. There's on average an order of magnitude of difference between the two.

Am I running into a strange edge case? Is the strange index key chosen by MariaDB at fault?

Answer 1

A kind sir from MariaDB's community slack helped me:

ANALYZE TABLE tbl PERSISTENT FOR ALL;

'Fixes' the table.

Answer 2

Start by installing WP Index Improvements

If you still have problems, please provide the current results of SHOW CREATE TABLE wp_posts and SHOW CREATE TABLE wp_postmeta. And fresh EXPLAINs.

The inability to directly use numeric values (cf ORDER BY wp_postmeta.meta_value + 0) is an intrinsic deficiency in WP.