Woocommerce Question posted in
The official Woocommerce documentation can be found here.

WordPress website with extreme CPU usage due to slow queries – Woocommerce

I am working on a website where I work with Advanced custom fields and woocommerce. I have 1800 products which can be created and edited by members (of course each member can only edit their own product). Since each product has about 40 custom fields, the database is growing rapidly, and with the plugin ‘query monitor’ I see that the query I run to get those products are really slow (0,06 – 0,08).
How can I optimize this? I use redis and OPCache, but the website is not accessible for alot of time.

This is one of the queries that I am running:


SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1
ON ( wp_posts.ID = mt1.post_id )
WHERE 1=1
AND wp_posts.post_author NOT IN (1)
AND ( wp_postmeta.meta_key = 'order_cal'
AND ( ( ( mt1.meta_key = '_product_sub'
AND mt1.meta_value NOT IN ('') ) ) ) )
AND wp_posts.post_type = 'product'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value DESC

I heard you can optimize your table with indexes, but could not find a decent tutorial to help in my specific case.

Thanks in advance, all help is welcome 🙂

Tags:

2

Answers


  1. 0

    For wp_postmeta:

    INDEX(post_id, meta_key)

    See this for more details: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

    Login or Signup to reply.
  2. 0

    You need to create and indexes in SQL, Please check the below indexes and run your query after adding these.

      ALTER TABLE `wp_postmeta` ADD INDEX `wp_postmeta_idx_meta_key_post_id_meta_value` (`meta_key`,`post_id`,`meta_value`);
    ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_type_post_statu_post_autho` (`post_type`,`post_status`,`post_author`);
    ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_type_post_statu_id` (`post_type`,`post_status`,`ID`);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search