A database is an organized collection of all your website’s data. In a WooCommerce store, it includes:
- Site content such as product pages, categories, tags, user data, reviews, site-wide settings, theme, plugins, etc.
- Transaction data such as order details, payments, inventory, etc.
Every time a user visits your store, they’re requesting your site content, which is mostly static and doesn’t change much. But when they’re placing an order, they’re making a dynamic request.
If you don’t optimize your store’s database, it may take too long to process these requests. Ultimately, a slow server response time leads to a slow website. Hence, you must clean up and optimize the database by removing unnecessary junk.
Here are 6 ways to speed up and fine-tune your WooCommerce database.
Delete Old Revisions
If your WooCommerce store has been up for quite a while, its pages, posts, and products will be filled with old revisions. It’s time to clean these up.
The easiest way to do it is through plugins such as WP-Sweep.
If you’re handy with WP-CLI, then you can also connect to your server via SSH and run a few simple commands to do the same. Here’s how you can do it.
Limit the Number of Revisions Stored
You can avoid having numerous old revisions by limiting the number of revisions for posts and pages. If you’re prone to updating your site content often, this will help you keep revisions from going out of hand.
An easy way to set the limit is by adding the below code snippet below to your wp-config.php file. Make sure to add it before the line where ABSPATH is defined.
define('WP_POST_REVISIONS', 10);
I’ve set the limit as 10 in the code above. You can change it to any number of revisions you want, but try to keep it below 10.
Alternatively, you can use a free plugin such as WP Revisions Control to do the same.
Disable Revisions Altogether (If They’re Unnecessary)
You can disable revisions from your site altogether. All you need to do is add the following code to your wp-config.php file. As before, ensure that you add this snippet above the line where ABSPATH is defined.
define('WP_POST_REVISIONS', false);
Or you can use a free plugin such as Disable Post Revision to do the same with a single click.
Before disabling revisions, we suggest you delete all the old revisions that are still present. This way, your site’s database will be completely free of revisions going forward.
Clean up Expired Transients
Transients are cached data stored with a custom name and an expiration period. They’re usually stored in the WordPress database under the wp_options table.
Transients are similar to Options, except they have an added property of expiration time. They are useful to store temporary data such as responses from a remote API or large queries.
The WordPress Transients API defines how to handle transients and delete expired transients automatically. However, things don’t always go as planned.
Sometimes, a corrupt transient cache can create almost a million junk entries with no end in sight. Customer sessions can rack up over time, creating thousands of unnecessary rows in your database tables. In such cases, a bloated database can bring your site to its knees.
The free Delete Expired Transients plugin is an easy way to get rid of all expired transients.
Clean up Your Database Tables
Your WooCommerce database stores everything that’s needed to run your ecommerce store. As data gets added, removed, and moved around the tables in your database, it becomes inefficient and ends up storing many things that aren’t important to run your store.
You need to clear out this unnecessary data by cleaning up your database tables. The wp_options table, in particular, is prone to getting bloated and hurting database performance.
Database housekeeping is key to keeping your WordPress site running as swiftly as possible.
Optimizing your database manually is a time-consuming process. You can use the free WP-Optimize plugin to check which database tables have unnecessary data and delete those wasted spaces. It can also retrieve storage space lost to data fragmentation.
You can schedule WP-Optimize to clean up and optimize your database regularly.
Disable Non-Critical Features That Tax the Database
WooCommerce themes and plugins come with a lot of cool features that seem great from the outset but end up crumbling the database.
One such example is using “Popular Products” and “Related Products” plugins.
These features can cause enormous sitewide queries, especially if you have many products. If you need these features, try to insert them manually into your pages. Plugins such as Advanced Custom Fields can help here immensely.
Another example is image optimization plugins that compress images on the same server where the site is hosted, rather than do it externally. This ends up using a lot of your server’s resources.
Also, stay away from plugins that add counters to your site. For example, adding the number of views/posts/comments beside a username. A lengthy discussion with multiple users involved will stress your database to crunch these numbers.
This advice holds true for using social counters too. Minimize the use of auto-generated counters to maximize database performance.
Like housekeeping, fine-tuning your WordPress database is an ongoing process and here are a few database optimization plugins to make your job easier:
- WP-Sweep
- WP-Optimize
- WP Clean Up Optimizer
- Advanced Database Cleaner
Reminder: Always take a backup before editing your database or WordPress core files.