skip to Main Content

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:

enter image description here

2

Answers


  1. The first thing you can try doing is trimming what data you fetch.

    That means:

    1. Not fetching fields that you don’t need/check
    2. Implementing our constrains before joining
    
    SELECT 
        p.ID order_id 
    FROM 
        (SELECT id, post_status, post_date FROM wp_posts WHERE post_status = 'wc-completed') p, 
        (SELECT order_id, order_item_id FROM wp_woocommerce_order_items) i,
        (
            SELECT 
                order_item_id,
                meta_key,
                meta_value 
            FROM 
                wp_woocommerce_order_itemmeta 
            WHERE 
                meta_key IN ('_product_id','_variation_id')
                AND meta_value IN ('703899','981273','981274','981275')
        ) im
    WHERE 
        p.ID = i.order_id
        AND i.order_item_id = im.order_item_id 
    GROUP BY 
        p.ID 
    HAVING 
        COUNT(p.ID)>1 
    ORDER BY 
        p.post_date desc 
    LIMIT 
        0, 20
    
    

    Edit:

    If Inner joins are necessary, you can try:

    
    SELECT 
        p.ID order_id 
    FROM 
        (SELECT id, post_status, post_date FROM wp_posts WHERE post_status = 'wc-completed') p 
    INNER JOIN 
        (SELECT order_id, order_item_id FROM wp_woocommerce_order_items) i 
    ON 
        p.ID = i.order_id 
    INNER JOIN 
        (
            SELECT 
                order_item_id,
                meta_key,
                meta_value 
            FROM 
                wp_woocommerce_order_itemmeta 
            WHERE 
                meta_key IN ('_product_id','_variation_id')
                AND meta_value IN ('703899','981273','981274','981275')
        ) im
    ON 
        i.order_item_id = im.order_item_id 
    GROUP BY 
        p.ID
    HAVING 
        COUNT(p.ID)>1 
    ORDER BY 
        p.post_date desc 
    LIMIT
        0, 20
    
    

    ps* I hope my syntax is correct ˙ my SQL is quite rusty

    Login or Signup to reply.
  2. Why do you join when you only want to select IDs from wp_posts anyway?

    SELECT p.ID order_id 
    FROM wp_posts p 
    WHERE p.post_status = 'wc-completed'
    AND p.ID IN
    (
      SELECT i.order_id
      FROM wp_woocommerce_order_items i
      JOIN wp_woocommerce_order_itemmeta im ON im.order_item_id = i.order_item_id
      WHERE im.meta_key IN ('_product_id','_variation_id') 
        AND im.meta_value IN ('703899','981273','981274','981275') 
      GROUP BY i.order_id
      HAVING COUNT(*) > 1
    )
    ORDER BY p.post_date DESC 
    LIMIT 0, 20;
    

    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:

    create index idx1 on wp_posts(post_status, id, post_date);
    create index idx2 on wp_woocommerce_order_items(order_id, order_item_id);
    create index idx3 on wp_woocommerce_order_itemmeta(order_item_id, meta_key, meta_value);
    

    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:

    create index idx4 on wp_woocommerce_order_itemmeta(meta_key, meta_value, order_item_id);
    create index idx5 on wp_woocommerce_order_items(order_item_id, order_id);
    create index idx6 on wp_posts(id, post_status, post_date);
    

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search