Question 2010 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Query Optimization with Indexes – WordPress

I am needing some assistance on optimizing this WordPress/WooCommerce query:

SELECT
    p.ID AS order_id
    ,DATE(p.post_date) AS order_date
    ,SUBSTR(comment_content,17) AS csr
    ,SUBSTR(p.post_status,4) AS order_status
    ,UCASE(CONCAT((SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE meta_key = '_billing_first_name' and wp_postmeta.post_id = p.ID),' ',(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE meta_key = '_billing_last_name' and wp_postmeta.post_id = p.ID))) AS customer
    ,(SELECT GROUP_CONCAT(DISTINCT order_item_name ORDER BY order_item_name ASC SEPARATOR ', ') FROM wp_woocommerce_order_items WHERE order_id = p.ID AND order_item_type = 'line_item' GROUP BY order_id) AS products
    ,(SELECT GROUP_CONCAT(CONCAT(serial_number,'',serial_feature_code)) FROM wp_custom_serial WHERE wp_custom_serial.order_id = p.ID GROUP BY wp_custom_serial.order_id) AS serials 
FROM
    wp_posts AS p
    INNER JOIN wp_comments AS c ON p.ID = c.comment_post_ID
    INNER JOIN wp_postmeta AS pm ON p.ID = pm.post_id
    
WHERE
    p.post_type = 'shop_order'
    AND comment_content LIKE 'Order placed by%'
GROUP BY p.ID
ORDER BY SUBSTR(comment_content,17) ASC, p.post_date DESC;

I do not understand what EXPLAIN is telling me and need some guidance on how to speed it up. Can someone describe what, in the EXPLAIN response, indicates where my issue is and where to look for answers?

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY c NULL ALL comment_post_ID NULL NULL NULL 20452 11.11 Using where; Using temporary; Using filesort
1 PRIMARY p NULL eq_ref PRIMARY,post_name,type_status_date,post_parent,post_author PRIMARY 8 db.c.comment_post_ID 1 50.00 Using where
1 PRIMARY pm NULL ref post_id post_id 8 db.c.comment_post_ID 33 100.00 Using index
2 DEPENDENT SUBQUERY wp_postmeta NULL ref post_id,meta_key post_id 8 func 33 2.26 Using where
3 DEPENDENT SUBQUERY wp_postmeta NULL ref post_id,meta_key post_id 8 func 33 2.30 Using where
4 DEPENDENT SUBQUERY wp_woocommerce_order_items NULL ref order_id order_id 8 func 2 10.00 Using where
5 DEPENDENT SUBQUERY wp_custom_serial NULL ALL NULL NULL NULL NULL 5160 10.00 Using where; Using filesort
Tags:

2

Answers


  1. 0

    Queries are processed in distinct stages. The first clauses processed are the FROM, then WHERE, and then the SELECT clause. Those dependent subqueries mean that for each row that you "have" after processing the FROM and WHERE clauses you are running separate, new subqueries for each row of those results. In your case you are doing that times four.

    You can usually rework this to move these queries out of the SELECT clause and into the FROM clause.

    Taking one column you have, the serials column, I think you would want to move that into the FROM clause in a way like this

    SELECT p.ID                                                                                  AS order_id
     , DATE(p.post_date)                                                                     AS order_date
     , SUBSTR(comment_content, 17)                                                           AS csr
     , SUBSTR(p.post_status, 4)                                                              AS order_status
     , UCASE(CONCAT((SELECT wp_postmeta.meta_value
                     FROM wp_postmeta
                     WHERE meta_key = '_billing_first_name' and wp_postmeta.post_id = p.ID), ' ',
                    (SELECT wp_postmeta.meta_value
                     FROM wp_postmeta
                     WHERE meta_key = '_billing_last_name' and wp_postmeta.post_id = p.ID))) AS customer
     , (SELECT GROUP_CONCAT(DISTINCT order_item_name ORDER BY order_item_name ASC SEPARATOR ', ')
        FROM wp_woocommerce_order_items
        WHERE order_id = p.ID
          AND order_item_type = 'line_item'
        GROUP BY order_id)                                                                   AS products
     , serials_sub.serials
FROM wp_posts AS p
         INNER JOIN wp_comments AS c ON p.ID = c.comment_post_ID
         INNER JOIN wp_postmeta AS pm ON p.ID = pm.post_id
         LEFT JOIN (
            SELECT p.ID as post_id, GROUP_CONCAT(CONCAT(cs.serial_number, '', cs.serial_feature_code)) AS serials
            FROM wp_custom_serial cs
            JOIN wp_posts AS p ON cs.order_id = p.ID
            WHERE p.post_type = 'shop_order'
             AND comment_content LIKE 'Order placed by%'
            GROUP BY cs.order_id
         ) as serials_sub ON serials_sub.post_id = p.ID
WHERE p.post_type = 'shop_order'
  AND comment_content LIKE 'Order placed by%'
GROUP BY p.ID
ORDER BY SUBSTR(comment_content, 17) ASC, p.post_date DESC;

    The difference here is that instead of separate queries being performed for each row, a single subquery is used in the initial FROM clause. So while perhaps looking more unwieldy, in fact this will give you much better performance.

    Following this pattern for the other subqueries I think will resolve your issues.

    If interested here is the documentation on the EXPLAIN.

    https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html

    And I recommend the book High Performance MySQL.

    Login or Signup to reply.
  2. 0
    • The outer wp_postmeta does not seem to be used. Remove the JOIN if possible.
    • Hiding information in the middle of strings leads to SUBSTR() usage, which is inefficient.
    • The GROUP BY p.ID seems to be unnecessary.
    • The plugin WP Index Improvements would help with some parts.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search