1

I am optimizing the database of my site that is using WooCommerce.

When I came across the wp_comments table, I found it close to 100 MB in total. It's all filled with WooCommerce order statuses from last few years.

What's the best way to decrease the size of my database?

  • if you don't need the order status, you can remove row if older than X date – bynicolas Aug 25 '16 at 2:40
  • Are you sure that the data is stored in the wp_comments table? I see there is a wp_woocommerce_order_items and wp_woocommerce_order_itemsmeta table that may be related. – Ethan Jinks O'Sullivan Aug 25 '16 at 3:25
  • @bynicolas there are lots of rows as i told its close to 100mb – H.Ahuja Aug 25 '16 at 3:33
  • @EthanJinksO'Sullivan I think Woocommerce does use the comments table to store order statuses – bynicolas Aug 25 '16 at 3:34
  • Hm, I see. @H.Ahuja did you already try removing all of your order status from the last few years through the WordPress Dashboard? – Ethan Jinks O'Sullivan Aug 25 '16 at 3:36
0

To delete rows in your wp_comments you can use an SQL statement like so

DELETE FROM wp_comments where comment_date < '2016-01-01' and comment_type = 'order_notes'

This will delete all comments older than January 1st 2016. I do believe Woocommerce uses order_notes as a comment_type make sure by checking that column before running the statement.

Of course, you need to adapt the FROM wp_comments to reflect the prefix you are using in your DB.

Also make a backup of your DB BEFORE running this statement, it will ERASE your data!

EDIT

here's an alternative statement

DELETE FROM wp_comments where comment_date < DATE_ADD(now(),INTERVAL -365 DAY) and comment_type = 'order_notes'

It does the same job but the SQL statement will generate the today date and remove 365 days from it and delete everything older than 365 days. I find it easier to work with. You could run this statement every month and it will always remove entries older than 365 days from run time.

  • Thanks for these but i am afraid of using these,as data in comments table is linked to other tables like commentmeta .Do you know where order status notes in comments table are eventually used? – H.Ahuja Aug 25 '16 at 21:02
  • well you asked how to shrink the size of wp_comments, this is how. Now you indeed would need to make an additional check by querying the wp_comments before and retrieving IDs of the rows you are about to delete. Then remove corresponding rows in wp_commentmeta if you wish to keep this clean also. As of to where order status notes are used... I don't know. I would assume that they used it only in the order post to display relevant info but I would recommend to check with Woocommerce forums to make sure of that. – bynicolas Aug 25 '16 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.