19 Nov Don’t put free downloadable products in WooCommerce
This weekend I have seen a shop basically go down in flames because they had “sold” thousands upon thousands (nearly 100k, by my guess) of free downloadables via WooCommerce on their website. They don’t have scalable hosting, so any time they run a WooCommerce-based query it basically takes down the database server. Their database is currently 1.3 Gb. After fruitlessly attempting to migrate, export, optimize, or anything else, the only recommendation I was left with was to delete the backlog of free order data and stop listing free products. I have two other shops on the verge of the same situation, for the same reason.
By comparison, another extremely active shop that does not list free downloadables has a database of 500Mb.
Let me be clear: the root of the problem is not the users. It’s WooCommerce’s extremely inefficient techniques for storing large amounts of data per order. But since we can’t expect that to change in a well established plugin, we need to address the users instead.
Let’s break it down. The tables containing the most WooCommerce data are posts and postmeta, comments and comment meta, woocommerce_downloadable_product_permissions, woocommerce_order_item_meta, woocommerce_order_items, and woocommerce_sessions.
Posts and postmeta store primary order data like order number, date, time, products ordered, etc. Comments and comment meta store everything that goes into the “notes” area, such as payment status and inventory adjustments. The rest of the tables are self-labeled.
TL;DR – There is no solution to this problem other than to keep your database clean and don’t sell free products. Clean out spam comments regularly, clean out post revisions, empty the trash, delete canceled orders, optimize your database, and delete woocommerce transients. If your store gets so huge that your database is unmanageable you need to switch to another platform like Shopify.