我需要一些帮助来优化这个WordPress/WooCommerce查询：
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;
我不明白EXPLAIN在告诉我什么，需要一些关于如何加快速度的指导。有人能在EXPLAIN响应中描述什么表明我的问题在哪里以及在哪里寻找答案吗？
查询是在不同的阶段处理的。处理的第一个子句是from，然后是WHERE，然后是SELECT子句。这些从属子查询意味着，对于您在处理从和WHERE子句后“拥有”的每一行，您将分别运行这些结果的每一行的新子查询。在您的情况下，您将执行四次。
您通常可以对此进行修改，将这些查询从SELECT子句移出并移到从子句中。
以您拥有的一列，即系列列为例，我认为您希望以如下方式将其移动到从子句中
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;
这里的不同之处在于，不是为每一行执行单独的查询，而是在初始从子句中使用单个子查询。因此，虽然看起来更笨拙，但实际上这将为您提供更好的性能。
我认为其他子查询遵循此模式将解决您的问题。
如果感兴趣，这里是解释上的留档。
https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html
我推荐《高性能MySQL》一书。
SUBSTR（）的使用，效率低下。