The database is often the part of WordPress that slows down first, because it grows with everything: posts, products, orders, plugin settings and data nobody cleans up. Typical signs are a sluggish dashboard, product filters that take a long time, and pages that are slow even when the server otherwise has headroom.
Here we go through how to find out whether it’s the database, and what to do about it, in the order that usually pays off. If you’d like the overview of how WordPress is built first, start there.
Is it the database?
Don’t guess. Install Query Monitor, preferably on a copy of the site, and open one of the slow pages. It shows how many queries the page runs, how long they take in total, which are the slowest, and which plugin or theme is behind each of them. If a large part of the response time goes into the database, you’re in the right place. If it goes into PHP, it’s the code that needs looking at.
Query Monitor only sees the page you open yourself. To catch what’s slow for everyone else, you can turn on MySQL’s slow query log. It writes every query that takes longer than a limit you choose to a file:
# my.cnf / mariadb.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5On shared hosting you rarely have access to that file, but many providers can turn the log on for you.
The heavy queries
Most slow WordPress sites don’t have many slightly slow queries, but a few very slow ones. And they often look alike.
The most common is a filter on a field’s value. All extra fields live in wp_postmeta, and the value column isn’t indexed. A meta_query that looks for products with a particular color or sorts by a custom field forces the database to read every row with that key and compare them one by one. With a few hundred products, nobody notices. With tens of thousands, it takes seconds. Searches with LIKE '%word%' have the same problem, because an index can’t be used when the search term can appear anywhere in the text.
The next is lists without a limit. posts_per_page set to -1 fetches everything, which is fine on a blog with twenty posts, but not in a store. And even when the list is limited, WordPress counts the total number of results by default so it can show page numbers. If you don’t need page numbers, you can turn off the count and fetch only what you need:
$q = new WP_Query( [
'post_type' => 'product',
'posts_per_page' => 12,
'no_found_rows' => true, // no count when there's no pagination
'fields' => 'ids', // only IDs, if the rest comes from cache
] );WooCommerce has done a lot itself to get around wp_postmeta. Price, stock status and ratings also live in the lookup table wc_product_meta_lookup, which filters and sorting use, and orders live in their own tables with High-Performance Order Storage. If your store is older and still keeps orders in wp_posts, you can switch under WooCommerce → Settings → Advanced → Features. The biggest gain therefore often comes from finding the plugin that still runs its own heavy meta lookups, and getting it fixed or replaced.
Indexes: what WordPress has, and what it lacks
WordPress creates indexes on what the core itself looks up: post_id and meta_key in wp_postmeta, the type and status in wp_posts and so on. The value in wp_postmeta isn’t indexed, and that’s what the heavy filters hit. Before you add anything, see what the database actually does with the query. Copy it from Query Monitor and put EXPLAIN in front:
EXPLAIN SELECT post_id FROM wp_postmeta
WHERE meta_key = 'color' AND meta_value = 'blue';
-- If it reads many rows, a combined index can help:
ALTER TABLE wp_postmeta ADD INDEX key_value (meta_key(191), meta_value(100));The rows column in the result shows how many rows the database expects to read. Run EXPLAIN again after the change, and take a backup first: on a large table, building the index can take a while. The plugin Index WP MySQL For Speed adds a well-tested set of indexes to those tables if you don’t want to write them yourself.
wp_options and autoload
Settings marked for autoloading are fetched on every single request, whether the page uses them or not. Plugins that store logs, statistics or large configurations there therefore make every page a little heavier, and the data often stays behind after the plugin is deleted. This query shows the largest:
SELECT option_name, ROUND(LENGTH(option_value) / 1024) AS kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY LENGTH(option_value) DESC
LIMIT 20;The name usually reveals which plugin the row belongs to. If it belongs to a plugin you no longer use, it can be deleted. Otherwise you can turn off autoloading for it, but only if you know the plugin fetches it itself when it needs it.
Cleanup: what grows by itself
| What | Where it lives | How to keep it down |
|---|---|---|
| Revisions of posts and pages | wp_posts and wp_postmeta | Limit them in wp-config.php with define( 'WP_POST_REVISIONS', 5 ); and delete the old ones with a cleanup plugin |
| Expired transients | wp_options, when there’s no object cache | wp transient delete --expired with WP-CLI or a cleanup plugin |
| Spam and trashed comments | wp_comments and wp_commentmeta | Empty them, and close comments if you don’t use them |
| Completed WooCommerce background jobs | wp_actionscheduler_actions and _logs | Old jobs are deleted after 30 days by default; check that it happens if the tables are large |
| Data from deleted plugins | Their own tables, wp_options and wp_postmeta | Find them by table and key names, and delete them after a backup |
Take a backup before you delete anything directly in the database. OPTIMIZE TABLE can reclaim space after a big cleanup, but on InnoDB tables it rebuilds the whole table, so run it outside busy hours and only after a cleanup, not as a routine.
Object cache: fewer queries, not faster ones
A persistent object cache with Redis or Memcached stores the results of queries and calculations between requests, so the same thing doesn’t have to be fetched again and again. For an online store, where cart and checkout can’t be page-cached, it takes a large part of the load off the database. But it doesn’t make a slow query faster; the first visitor still pays the full price, and queries that depend on the individual customer are rarely reused. Fix the heavy queries first, and put the cache on top.
The server
The most important setting on a MySQL or MariaDB server for WordPress is innodb_buffer_pool_size: the memory the database keeps data and indexes in. If the frequently used data can stay in memory, the database rarely has to read from disk. Old guides recommend turning up query_cache_size, but the query cache no longer exists in MySQL 8, and in MariaDB it’s off by default. Distance matters too: if the database is on a different server from PHP, network time is added to every single query. On managed hosting the provider controls most of this, so ask what’s been set up.
Frequently asked questions
How often should I clean up the database?
Revisions and transients can be kept down automatically with the right settings. Beyond that, a look at table sizes and autoload a couple of times a year is enough for most sites, plus after you’ve removed a plugin.
Can I break something?
Yes. A wrong DELETE or a deleted option that a plugin still uses can take the site down. Always take a backup, and test on a copy before you make changes directly in the database.
Does switching to MariaDB or a newer MySQL help?
A newer version is usually a little faster and better at planning queries, but it won’t rescue a query that reads the whole of wp_postmeta. Fix the query, and upgrade the version when your hosting offers it.
Next steps
The database is one half of the server’s response time. The other is the code that runs, and we cover that in the guide to the PHP side of performance. If you have a store where filters, the admin or checkout have become slow, you can also get help with a slow WooCommerce database from us.