Clean up / Trim down wp_woocommerce_order_itemmeta Table?
I run a large Woocommerce site with 800+ products and 2,000 unique visitors a day, receiving 50+ orders a day.
Lately we’ve had long load times on the backend on the “orders” page when filtering. Makes our job very difficult.
I believe I’ve identified the culprit – the wp_woocommerce_order_itemmeta table has approx 571,000 rows.
I am curious, is there a way to clean out this table? We have around 11,000 completed orders stored in the database – I don’t want to lose my customer’s history…. I can’t imagine that all 571,000 rows pertain to only 11,000 orders.
I’m not really a database person (although I’m realizing with a site this size I’m going to have to be soon)…
Any help would be greatly appreciated.
Well, for those of you reading this through google searches and stuff, I just wanted to update and maybe help some people out.
I’ve been able to keep my Woocommerce site going. We’re up to about 1,350 products and 4,200 unique sessions a day, processing about 800 orders a week.
The back end has never been extremely fast. Woocommerce’s queries are just brutal. This is running on a dedicated server (dual Xeon quad core 2.4ghz with 24GB ram)
So, the things I’ve done to keep our site usable:
– About once every 3-5 days I have to “clear ALL Sessions” on the System status -> Tools page in Woocommerce. For some reason Woocommerce does not like to clear out all the dead sessions and abandoned carts.
– Unless you have to display more, only show 25 orders or products per page on the “Orders” page and the “Products” page. It will make viewing your orders a little less aggravating.
– We made the decision to only keep 1 years worth of orders in our history, and archive the rest. Of course, 1 year of orders is over 33,000 for us right now – so, the site is still pretty slow. I think I could safely use Woocommerce up until about 100,000 orders per year – then I’ve got a problem.
– Ensure that you keep your other WordPress tables clean. I use WP-Optimize to clear transients and stuff.
– Index your wp_options table. First, do this query to see what the distribution looks like:
SELECT COUNT(*), autoload FROM wp_options GROUP BY autoload;
if a large majority of them are set to ‘no’, you can solve some slow searches by adding an index on autoload.
ALTER TABLE wp_options ADD INDEX (
– The following SQL statements have kept things running smoothly on the front end – (USE AT OWN RISK — TO SEE WHAT YOU ARE DELETING BEFORE RUNNING, USE “SELECT *” instead of “DELETE”:
DELETE FROM wp_comments WHERE comment_approved = “spam”;
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE meta_key LIKE “%akismet%”;
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
Hopefully this is helpful to someone.
If anyone has any suggestions for me don’t hesitate to post!!!
- The topic ‘Clean up / Trim down wp_woocommerce_order_itemmeta Table?’ is closed to new replies.