WordPress Database Optimization: Speeding Up MySQL for WooCommerce

When a WooCommerce store gets slow, the first suspects are usually images, themes and plugins. Those matter, but in stores that have been running for a few years the real bottleneck is often the database. Every product page, cart update and admin screen runs queries against MySQL or MariaDB, and a database that has grown without care answers those queries more and more slowly. WordPress database optimization is the work of making that layer lean and fast again.

This guide explains where WordPress and WooCommerce databases accumulate weight, how to find the queries that actually hurt, which clean-ups are safe, and which server settings make the biggest difference. It is written for store owners and the people who look after their sites. You do not need to be a database administrator to follow it, but you do need a backup before you change anything.

Why the Database Slows Down a WooCommerce Store

WordPress stores almost everything in a small number of tables. Posts, pages, products, orders in older stores, menu items and revisions all live in the posts table. Their details live in the postmeta table as key and value pairs. Settings live in the options table. This flexible design is one reason WordPress is so extensible, and it is also why large sites slow down: the tables grow very large, and many queries have to search through metadata that was never designed for fast filtering.

Symptoms that point to the database

• The admin area is slow, especially the orders and products lists, even when the public site is cached.
• Time to first byte is high on uncached pages such as the cart, checkout and account pages.
• Pages slow down at particular times, for example when scheduled tasks run or during sales.
• The hosting control panel shows high CPU use by the database process.

Page caching hides many of these problems for anonymous visitors, but carts, checkouts and logged-in customers cannot be fully cached. That is exactly where a store earns money, so database performance directly affects conversion. The broader picture of speed is covered in our guide to website speed and Core Web Vitals.

Where the Weight Accumulates

Autoloaded options

WordPress loads every option marked as autoload on every single request. Plugins often store large arrays there, and many leave them behind after being deleted. A few hundred kilobytes of autoloaded data may not sound like much, but it is read on every page view. Recent WordPress versions include a Site Health check that warns when autoloaded options become too large, which makes this easier to spot than it used to be.

Expired transients

Transients are temporary cached values stored in the options table when no persistent object cache is available. Expired ones are cleaned up over time, but on busy sites or with poorly written plugins, thousands can accumulate.

Post revisions and auto-drafts

Every save of a product or page can create a revision. Stores that edit products often, or that import products regularly, can end up with more revisions than live content.

Orders stored as posts

Historically, WooCommerce stored orders in the posts and postmeta tables, mixing them with content. A store with tens of thousands of orders therefore had millions of metadata rows. WooCommerce’s High-Performance Order Storage moves orders into dedicated tables designed for them. The WooCommerce HPOS documentation explains how to enable it and check plugin compatibility. It is enabled by default for new stores, but many older stores still run on the legacy storage.

Scheduled action logs

WooCommerce and many plugins use a background job queue that logs completed and failed actions in their own tables. Without regular clean-up, these tables can grow to millions of rows, and the queries that pick the next job become slow.

Leftover plugin tables

Plugins that have been removed often leave their own tables behind: old statistics, logs, abandoned-cart data and search indexes. They may not slow down queries directly, but they bloat backups and make restores slower.

Measure Before You Clean

Optimising without measuring usually means deleting things that did not matter. Start with evidence.

1. Check table sizes. A simple query against the information schema, or the database view in phpMyAdmin, shows which tables are largest. Sort by data plus index size.
2. Look at slow queries. The slow query log records statements that take longer than a threshold you set, for example one second. A day of logging on a live store shows which queries hurt most.
3. Profile page requests. A query monitoring plugin in a staging copy shows how many queries each page runs, how long they take and which plugin runs them.
4. Measure the autoload size. Sum the size of autoloaded options and list the largest ones with the option names, which usually reveal the plugin responsible.

Write the numbers down. After the work, measure the same things again so you know what actually changed.

Safe Clean-Up Steps

These steps are low risk when done with a fresh backup and, ideally, first on a staging copy of the site.

Limit and remove old revisions

Set a revision limit in the WordPress configuration so each item keeps only the last few versions, then remove older revisions. Keep enough history for editors to undo mistakes.

Clear expired transients

Delete expired transients with a maintenance tool or WP-CLI. If they return in large numbers, find the plugin that creates them.

Fix autoloaded options

For large autoloaded options that belong to deleted plugins, remove them. For options that belong to active plugins but are not needed on every request, setting autoload to off can help, but test carefully, because some plugins expect their data to be loaded.

Trim job logs and sessions

Remove completed and failed scheduled actions older than a sensible period, such as a month, and clear expired customer sessions. WooCommerce has built-in retention for some of this data; check that it is working.

Remove orphaned metadata and leftover tables

Metadata rows that point to deleted posts can be removed. Tables from removed plugins can be exported and then dropped. Always confirm a table is truly unused before removing it.

Structural Improvements That Pay Off

Clean-up removes clutter. Structural changes make the remaining data faster to query.

Persistent object caching

By default, WordPress caches query results only for the duration of a single request. A persistent object cache such as Redis keeps them in memory between requests, so repeated queries for the same options, menus or product data do not hit the database at all. For stores with logged-in customers, this is often the single largest gain. The WordPress handbook’s section on caching describes the options.

Database server settings

MySQL and MariaDB ship with conservative defaults. The most important setting for InnoDB tables is the buffer pool size, which decides how much data and index can be kept in memory. If the working set of a store fits in memory, most reads avoid the disk entirely. The MariaDB optimization and tuning documentation covers this and related settings. Tuning should be done by whoever administers the server, because the right values depend on the total memory and what else runs on the machine.

When the Server Itself Is the Limit

Some stores are simply too busy for the hosting they are on. On shared hosting, the database server is shared with many other sites and you cannot tune it. If the store has been cleaned and still struggles, moving the database to a VPS or dedicated server with enough memory, or to a managed database service, may be the right step. Our article on choosing between shared, VPS and dedicated hosting explains the trade-offs. Monitoring the database alongside the web server also helps you see whether growth is coming before customers feel it.

Keeping the Database Healthy Over Time

A single clean-up helps for months; a routine keeps the store fast for years. A sensible routine for a typical store looks like this:

• Weekly: automatic removal of expired transients and old scheduled action logs.
• Monthly: check table sizes, the autoload total and the slow query log for new offenders.
• After each plugin removal: check for leftover options and tables.
• Quarterly: review the database server settings against current memory use and traffic.
• Always: tested backups before any clean-up, with a restore tried at least occasionally.

This is part of the ongoing work our server administration service covers for stores that would rather not do it themselves.

Mistakes That Make Things Worse

Database work goes wrong in predictable ways. Avoiding these saves hours of recovery.

• Cleaning without a backup. A single wrong query can remove order data. Take a full database backup immediately before any clean-up and confirm it can be restored.
• Running heavy operations at peak time. Optimising large tables can lock them. Schedule this work for the quietest hours of the store, and warn the team.
• Deleting tables by name guess. A table with an unfamiliar prefix may belong to an active plugin. Check which plugin created it, and export it before dropping it.
• Adding indexes everywhere. Each index speeds up some reads and slows down every write. Add indexes only for queries you have measured as slow, and check the effect.
• Treating one fast test as proof. Compare the same pages, at similar traffic levels, before and after. One quick page load after a clean-up proves little.
• Ignoring the plugin that caused it. If a plugin fills the options table or the job log every week, cleaning up is only treatment. Configure it, replace it or report the problem to its developer.

Frequently Asked Questions

Is WordPress database optimization safe to do on a live store?

Most clean-up steps are safe with a fresh, tested backup, but the safest approach is to try them first on a staging copy. Structural changes such as enabling HPOS or adding indexes should always be tested before being applied to the live store.

Do database optimization plugins actually help?

They help with routine clean-up such as revisions, transients and orphaned metadata. They cannot fix inefficient queries from other plugins, missing object caching or an undersized database server, which are often the bigger problems.

What is HPOS and should I enable it?

High-Performance Order Storage stores WooCommerce orders in dedicated tables instead of the general posts tables. For stores with many orders it usually improves performance. Check that your plugins are compatible and test on staging before switching.

How large is too large for autoloaded options?

There is no single limit, but WordPress Site Health warns when autoloaded data becomes large. In practice, anything over a few hundred kilobytes deserves a look, and several megabytes almost always points to a plugin storing data it should not autoload.

Will a persistent object cache help if I already use page caching?

Yes. Page caching serves ready-made pages to anonymous visitors, while an object cache speeds up the uncached requests: carts, checkouts, account pages and the admin area.

How often should the database be optimised?

Automate the routine clean-up weekly and review performance monthly. A deeper review makes sense after major plugin changes, a large import or a noticeable slowdown.

The Bottom Line

WordPress database optimization is less about one magic setting and more about removing accumulated weight and giving the database the right structure and resources. Measure first, clean up revisions, transients, logs and leftover data with a backup in place, move WooCommerce orders to HPOS, add a persistent object cache and make sure the database server has enough memory. Then keep a simple routine going. The result is a store whose checkout, account pages and admin stay fast as the business grows, which is exactly where speed turns into revenue.