WooCommerce - excelling eCommerce
Clean up / Trim down wp_woocommerce_order_itemmeta Table? (2 posts)

  1. MaggardRazors
    Posted 1 year ago #

    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.


  2. MaggardRazors
    Posted 5 months ago #

    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 (autoload);

    - 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;

    DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%');

    Hopefully this is helpful to someone.

    If anyone has any suggestions for me don't hesitate to post!!!

Topic Closed

This topic has been closed to new replies.

About this Plugin

  • WooCommerce - excelling eCommerce
  • Frequently Asked Questions
  • Support Threads
  • Reviews

About this Topic