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;
option_name LIKE ('%\_transient\_%');
Hopefully this is helpful to someone.
If anyone has any suggestions for me don't hesitate to post!!!