Query Optimization with Indexes-CodePudding

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

CodePudding user response：

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.