We recently got issues where db became slow, we checked slow query log and the majority of the queries were in this format:
items ON (
mt_id ON (
mt_qty ON (
meta_key = 'qty')
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')
meta_key IN ('product_id', '_variation_id')
meta_value = 2230
QTY IS NOT NULL);
We saw it is coming from get_sold_last_days in:
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.