I have a sql query (see below) for wordpress which is taking around 4-5secs to get results. It gives all order ids which have a product/variation id in it.
I want to make it more fast, any help?
SELECT p.ID order_id
FROM wp_posts p
INNER JOIN wp_woocommerce_order_items i ON p.ID=i.order_id
INNER JOIN wp_woocommerce_order_itemmeta im ON i.order_item_id=im.order_item_id
WHERE im.meta_key IN ('_product_id','_variation_id')
AND im.meta_value IN ('703899','981273','981274','981275')
AND p.post_status IN ('wc-completed')
GROUP BY p.ID HAVING COUNT(p.ID)>1
ORDER BY p.post_date desc
LIMIT 0, 20
Above query EXPLAIN
:
2
Answers
The first thing you can try doing is trimming what data you fetch.
That means:
Edit:
If Inner joins are necessary, you can try:
ps* I hope my syntax is correct ˙ my SQL is quite rusty
Why do you join when you only want to select IDs from wp_posts anyway?
Now let’s think about how the DBMS can address this. It can look for posts with status ‘wc-completed’, if there are only few such rows and then check whether they represent an order with more than one of the desired items. This would ask for these indexes:
Or it could look for the desired products, see whether an order contains more than one of them and then check whther this relates to a post with status = ‘wc-completed’. That would ask for these indexes:
We don’t know which way the DBMS will prefer, so we create all six indexes. Then we look at the explain plan to see which are being used and remove the others. Maybe the DBMS even sees no advantage in using indexes here at all, but I find this unlikely.