How to Optimize WordPress Database Queries

How WordPress stores data

WordPress keeps almost everything in MySQL or MariaDB tables: wp_posts for content, wp_postmeta for custom fields, wp_options for settings, wp_users, wp_comments and more. Plugins add their own tables. Slow queries on these tables make pages slow.

Find slow queries

Query Monitor plugin

• Shows all queries of a page load.
• Highlights slow, duplicate or failing queries.
• Shows which plugin or theme ran each query.

New Relic or Datadog

Deep monitoring tools that track query time, throughput and bottlenecks.

MySQL slow query log

Add this to the MySQL config file:

slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 1

Restart MySQL. Queries that take longer than 1 second are logged. Turn the log off when you finish, because it adds overhead.

Common problems and fixes

1. Slow meta queries on wp_postmeta

Queries on custom fields can be slow:

SELECT * FROM wp_posts
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = 'price' AND wp_postmeta.meta_value > 100;

Add an index:

ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key, meta_value(20));

meta_value is a long text column, so index only its first characters. Back up the database before you change tables.

2. A heavy wp_options table

The autoload column in wp_options marks options that load on every page. If too many large options autoload, every request is slower. Check the size:

SELECT SUM(LENGTH(option_value)) AS autoload_size
FROM wp_options WHERE autoload = 'yes';

Find the biggest ones:

SELECT option_name, LENGTH(option_value) AS size
FROM wp_options WHERE autoload = 'yes'
ORDER BY size DESC LIMIT 10;

Set large, rarely used options from a removed plugin to no, or delete them.

3. Too many post revisions and transients

Limit revisions in wp-config.php:

define('WP_POST_REVISIONS', 5);

Delete old revisions, spam comments and expired transients with a cleanup plugin such as WP-Optimize, or with WP-CLI:

wp transient delete --expired

4. Queries without a LIMIT

Custom queries that fetch all posts use a lot of memory. Always use posts_per_page and no_found_rows => true when you do not need pagination.

5. Expensive queries inside loops

Avoid running a query for each post inside a loop. Fetch the data in one query, or use update_post_meta_cache so WordPress loads all the meta at once.

Cache query results

• Object cache. Install Redis or Memcached with a plugin so repeated queries come from memory.
• Page cache. Plugins such as WP Rocket, LiteSpeed Cache or W3 Total Cache serve ready pages.
• Transients. Store the result of heavy custom queries with set_transient().

Keep the database healthy

• Optimize tables from time to time:

wp db optimize

• Use a current PHP and MySQL version.
• Remove plugins you do not use, because many leave data behind.
• Use InnoDB tables and give MySQL enough memory (innodb_buffer_pool_size).

Quick recap

• Find slow queries with Query Monitor or the slow log.
• Add indexes, clean wp_options, and use an object cache.