0

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?

3
  • Hi, and welcome to dba.se! Please edit your question and remove the images for the reasons outlined in this post. Put in formatted text in their place!
    – Vérace
    Commented Oct 21, 2022 at 15:57
  • Your question is not about a query but this can give you some ideas Commented Oct 21, 2022 at 16:47
  • What versions of Percona/MySQL/MariaDB? Is the MySQL Explain really on Percona?
    – Rick James
    Commented Oct 21, 2022 at 18:01

2 Answers 2

2

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

ANALYZE TABLE tbl PERSISTENT FOR ALL;

'Fixes' the table.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.