Hi,
We recently got issues where db became slow, we checked slow query log and the majority of the queries were in this format:
SELECT SUM(mt_qty.meta_value) AS QTY
FROM wp_posts AS orders
LEFT JOIN wp_woocommerce_order_items AS items ON (orders.ID = items.order_id)
LEFT JOIN wp_woocommerce_order_itemmeta AS mt_id ON (items.order_item_id = mt_id.order_item_id)
LEFT JOIN wp_woocommerce_order_itemmeta AS mt_qty ON (items.order_item_id = mt_qty.order_item_id AND mt_qty.meta_key = 'qty')
WHERE orders.ID IN (
SELECT ID FROM wp_posts
WHERE post_date_gmt >= '2023-11-16 00:00:00' AND post_date_gmt <= '2023-11-16 11:23:13'
AND post_type = 'shop_order' AND post_status IN ('wc-processing', 'wc-completed')
) AND mt_id.meta_key IN ('product_id', '_variation_id')
AND mt_id.meta_value = 2230
GROUP BY mt_id.meta_value
HAVING (QTY IS NOT NULL);
We saw it is coming from get_sold_last_days in:
wp-content/plugins/atum-stock-manager-for-woocommerce/classes/Inc/Helpers.php
We added index to date_created_gmt and it solved the issue, but maybe it is better that the code would use date_created, which is indexed in wordpress by default.
Thanks,
Asaf
