MySQL performance tuning for WordPress: the 5 queries to check first

When a WordPress or WooCommerce site gets slower over the years, the database is usually involved. Not because MySQL is slow, but because WordPress's flexible schema makes it easy for plugins to store data in ways that are expensive to read.

These five checks find most database problems we see. Run them on a staging copy or a read replica, and take a backup before changing anything. Table names below assume the default wp_ prefix.

1. Autoloaded options

WordPress loads every autoloaded row of wp_options on every request, cached or not. Plugins often store large settings, logs or caches there and never clean up after uninstall.

Check the total size:

SELECT COUNT(*) AS options, ROUND(SUM(LENGTH(option_value)) / 1024) AS kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

Since WordPress 6.6 the autoload column can contain on, auto-on and auto in addition to the older yes; the query covers all of them. WordPress's Site Health screen warns when autoloaded options get large. Then find the biggest rows:

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;

Rows belonging to removed plugins can usually be deleted. Large rows from active plugins can often be set to not autoload, if the plugin only needs them on specific pages. Test after each change.

2. Lookups by meta value

wp_postmeta stores custom fields as key–value rows. meta_key is indexed; meta_value is not. Any query that searches by value, such as finding a product by SKU or an order by external reference, scans every row with that key.

Find these in the slow query log (check 5) or with Query Monitor, then run EXPLAIN on them:

EXPLAIN SELECT post_id FROM wp_postmeta
WHERE meta_key = '_sku' AND meta_value = 'ABC-123';

If rows is in the hundreds of thousands, the fix is in the code that issues the query: use WooCommerce's product lookup table (wc_product_meta_lookup, which has an indexed SKU column) or WooCommerce's own functions instead of raw meta queries. For orders, migrating to High-Performance Order Storage (HPOS) moves order data into dedicated, indexed tables.

3. WooCommerce sessions

WooCommerce stores cart sessions in wp_woocommerce_sessions. Expired sessions are removed by a scheduled task. If WP-Cron does not run reliably, for example because cron is disabled without a real system cron replacing it, the table grows without limit.

SELECT COUNT(*) AS total,
       SUM(session_expiry < UNIX_TIMESTAMP()) AS expired
FROM wp_woocommerce_sessions;

A large expired count means scheduled tasks are not running. Fix the cron setup first; the cleanup then takes care of itself. Check Tools → Scheduled Actions in WooCommerce for other overdue tasks while you are there.

4. Expired transients

Transients are temporary cached values. Without a persistent object cache they are stored in wp_options, and expired ones are not always deleted promptly.

SELECT COUNT(*) FROM wp_options
WHERE option_name LIKE '\_transient\_timeout\_%'
  AND option_value < UNIX_TIMESTAMP();

Thousands of expired transients bloat wp_options and slow down queries against it. wp transient delete --expired with WP-CLI removes them safely. The lasting fix is a persistent object cache (Redis or Memcached), which moves transients out of the database altogether.

5. The slow query log

The checks above cover common patterns. The slow query log shows what your site actually does. Enable it temporarily:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;

Let real traffic run for a few hours, then summarise the log with pt-query-digest from Percona Toolkit. It groups similar queries and ranks them by total time, which is what matters: a 50 ms query that runs 10,000 times an hour costs more than one 5-second report. Run EXPLAIN on the top entries and trace each one back to the plugin or theme that issues it. Turn the log off again afterwards, or raise long_query_time, so it does not fill the disk.

Beyond queries

If the queries are healthy and the database is still slow, look at the server: buffer pool size (innodb_buffer_pool_size) relative to the data, whether the database shares a machine with other busy sites, and the MySQL or MariaDB version. Those are hosting questions as much as code questions.

These checks are part of how we find a slow WooCommerce checkout. If you would rather have someone do it, our WordPress and e-commerce service includes performance tuning of the application, database and hosting.