Database Optimization: Speed Up MySQL for WordPress

Every time a visitor loads a WordPress page, the server makes 20 to 80 database queries. These queries retrieve post content, load settings, check user sessions, fetch widget data, and run plugin logic. When your database is small and well-indexed, each query takes 1–5ms. But as your site grows — accumulating thousands of posts, revisions, transients, and plugin data — queries slow down. A database that took 50ms to serve a page now takes 400ms or more.

Database optimization is the process of cleaning, restructuring, and tuning your database to handle queries efficiently. For WordPress sites running on MySQL or MariaDB, the optimizations in this guide can reduce query execution time by 30–70% and cut TTFB significantly.

Understanding WordPress Database Structure

WordPress uses 12 core database tables. Understanding what each stores helps you identify where bloat accumulates:

The wp_options Problem

The wp_options table is the single biggest performance bottleneck in most WordPress databases. Every page load queries this table to retrieve site settings, and WordPress loads all rows where autoload = 'yes' into memory. On a fresh WordPress install, autoloaded data is about 100KB. On a site with 30+ plugins, it can balloon to 2–10MB.

When autoloaded data exceeds 1MB, you'll notice measurable slowdowns. Above 5MB, every page load is significantly impacted because the server must load and parse all that data from the database into PHP memory.

Step 1: Clean Up Database Bloat

Delete Post Revisions

WordPress saves every draft and edit as a revision. A post edited 50 times has 50 revisions in the database. A site with 500 posts can easily have 10,000–25,000 revision rows in wp_posts and their associated metadata in wp_postmeta.

Limit revisions by adding this to wp-config.php:

define('WP_POST_REVISIONS', 5);

This keeps the 5 most recent revisions and stops unlimited accumulation. Then use a plugin like WP-Optimize to delete existing excess revisions.

Clean Expired Transients

Transients are temporary data stored in wp_options by plugins and WordPress core. They have expiration dates, but expired transients aren't always cleaned up. On a site that has run a few caching and import plugins over the years, expired transients can run into the thousands of rows and tens of megabytes. WP-Optimize and Advanced Database Cleaner can remove them in one click.

Remove Spam and Trashed Comments

WordPress doesn't permanently delete spam and trashed comments automatically. A site running for years can have thousands of spam comments. Delete them in bulk from the Comments page or use WP-Optimize.

Delete Orphaned Metadata

When you delete a post, its metadata in wp_postmeta sometimes remains. Same with comments, terms, and users. These orphaned rows take up space and slow down queries. Database cleanup plugins can identify and remove them.

Step 2: Optimize the wp_options Table

This is the highest-impact optimization for most WordPress sites. Here's how to diagnose and fix wp_options bloat:

Check Autoloaded Data Size

Run this SQL query in phpMyAdmin or your database tool:

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

If the result is over 1MB (1,000,000 bytes), you have autoload bloat. Over 5MB is critical.

Find the Biggest Offenders

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

This shows the 20 largest autoloaded options. Common culprits include:

• Plugin settings storing large serialized arrays
• Transients that are set to autoload (they shouldn't be)
• Analytics or logging data stored in options instead of custom tables
• Removed plugin data that wasn't cleaned up on deactivation

Fix Autoload Bloat

For plugin data you no longer need: delete the option row entirely. For active plugin data that doesn't need to load on every page: change autoload from 'yes' to 'no'. Only options accessed on every page load should be autoloaded.

Step 3: Add and Optimize Indexes

Indexes are the single most important factor in database query performance. An index lets the database find specific rows without scanning the entire table — like a book index lets you find a topic without reading every page.

WordPress Default Indexes

WordPress creates indexes on primary keys and some foreign keys by default, but several common query patterns lack proper indexes:

• wp_postmeta.meta_value — Not indexed by default. Queries like "find all products with price under $50" do full table scans.
• wp_options.autoload — Not indexed by default. The query that loads all autoloaded options scans the entire table.
• wp_posts.post_type + post_status — Composite queries benefit from a compound index.

Add an Autoload Index

This index dramatically speeds up the autoload query that runs on every page load:

ALTER TABLE wp_options ADD INDEX autoload_idx (autoload);

On a wp_options table with 10,000+ rows, this can reduce the autoload query time from 50ms to under 5ms.

Step 4: Enable Redis Object Caching

Object caching with Redis stores database query results in memory. When WordPress needs data it has already queried, Redis serves it from RAM (1–2ms) instead of hitting the database (5–50ms).

For WordPress, the Redis Object Cache plugin by Till Kruss is the standard solution. Once Redis is available on your server and the plugin is activated, WordPress automatically caches database queries in Redis.

Redis Impact on Database Load

The impact is especially significant on WooCommerce sites and membership sites that make hundreds of database queries per page.

Step 5: Optimize MySQL/MariaDB Configuration

If you have access to your MySQL configuration (my.cnf or my.ini), these settings can improve performance:

Key Buffer and Cache Settings

• innodb_buffer_pool_size — The most important setting. Set to 70–80% of available RAM on a dedicated database server, or 25–50% on shared servers. This caches table data and indexes in memory.
• query_cache_type = OFF — Counterintuitive, but MySQL's query cache is deprecated in MySQL 8.0+ and actually slows down writes. Use Redis object caching instead.
• innodb_log_file_size — Set to 256MB or 512MB for most sites. Larger log files improve write performance.
• max_connections — Set based on expected concurrent users. Too high wastes memory; too low causes connection errors. 100–200 is typical for most WordPress sites.

MariaDB vs. MySQL for WordPress

MariaDB is a drop-in replacement for MySQL that generally offers better performance for WordPress workloads. Key advantages:

• 5–15% faster query execution for common WordPress queries
• Better connection handling under load
• More aggressive optimizer that chooses better query plans
• Thread pool support (in MariaDB Enterprise) for better concurrency

Step 6: Regular Maintenance Schedule

Database optimization isn't a one-time task. Set up a regular schedule:

WP-Optimize can automate most of these tasks. Configure it to run weekly cleanups automatically so your database stays lean without manual intervention.

Diagnosing Slow Queries

Using Query Monitor

Query Monitor is a free WordPress plugin that shows every database query on a page, how long each took, and which component triggered it. Install it, load a slow page, and look for:

• Queries taking over 50ms — These are your bottlenecks
• Duplicate queries — The same query running multiple times indicates poor coding in a plugin or theme
• Queries without indexes — Query Monitor flags these automatically
• High total query count — Pages with 100+ queries likely have plugin or theme inefficiencies

Using the Slow Query Log

Enable MySQL's slow query log to capture queries that take longer than a threshold (typically 1 second):

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

Review the log periodically to identify recurring slow queries. These are often caused by missing indexes, poorly written plugin SQL, or tables that need optimization.

Frequently Asked Questions

How often should I optimize my WordPress database?

Run automated cleanups (transients, spam, revisions) weekly. Run table optimization (OPTIMIZE TABLE) monthly. Audit autoloaded data quarterly. For high-traffic sites with active WooCommerce stores, consider daily transient cleanup and weekly table optimization.

Is it safe to delete post revisions?

Yes. Post revisions are backup copies of previous edits. If you don't need to revert to a version from months ago, deleting old revisions is safe. Keep the 3–5 most recent revisions per post as a safety net, and delete the rest.

Will database optimization break my site?

Standard cleanup operations (deleting revisions, transients, spam comments) are safe. However, always back up your database before making changes, especially before adding indexes or modifying wp_options. If you accidentally delete an active option row, it could break a plugin.

How do I know if my database is the bottleneck?

Install Query Monitor and check the "Queries" panel. If total database query time is over 200ms, your database is a bottleneck. If TTFB is high but query time is low, the bottleneck is elsewhere (PHP processing, server resources, or network). Also check if your TTFB improves when page caching is enabled — if TTFB drops from 500ms to 30ms with caching, the uncached bottleneck is likely database and PHP.

Should I use MySQL 8.0 or MariaDB?

For WordPress, MariaDB generally performs better due to its more aggressive query optimizer and better compatibility with WordPress's query patterns. MariaDB 10.6+ is recommended. MySQL 8.0 works fine but may show slightly lower performance on complex WooCommerce queries.