WordPress database optimization guide (2026 edition)

WordPress stores everything in a MySQL (or MariaDB) database. Over time, this database “bloats”, accumulating unnecessary data, which can slow down queries and the entire site. A larger database means longer backup times, slower SELECT queries, and increased Time To First Byte (TTFB).

In this comprehensive guide for developers, we will go through advanced database optimization – from simple cleaning to query analysis and InnoDB configuration.

Why database optimization is critical IN 2026

In 2026, when a TTFB under 100ms is the standard, a “heavy” database is an anchor for your site. Even if you have a super-fast server (PHP 8.4, NVMe), poorly optimized SQL queries can kill performance.

Main causes of database problems:

1. Bloatware (Excess Data): Post revisions, comment spam, orphaned metadata.
2. Autoloaded Options: Data loaded on every page refresh, even if unused.
3. Missing Indexes: Queries that must scan the entire table (Full Table Scan).
4. Table Fragmentation: Gaps in data that increase file size on disk.

Part 1: Database hygiene (cleaning)

Let’s start by removing the trash. You can do this with a plugin (WP-Optimize), but as a developer, you should know how to do it “manually” using SQL or WP-CLI.

1. Post revisions

Every time you click “Save Draft”, a new copy of the post is created. With long editing sessions, you can have hundreds of revisions for a single article.

SQL Query to Check Revision Count:

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

Deleting Revisions (SQL):

DELETE FROM wp_posts WHERE post_type = 'revision';

Recommended: Use WP-CLI (Safer):

wp post delete $(wp post list --post_type='revision' --format=ids) --force

2. Cleaning spam and trash

There is no point in keeping spam or deleted posts.

SQL:

DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';

3. Transients (temporary data)

Transients are cache saved in the database (wp_options). Sometimes expired transients are not automatically removed.

SQL Cleaning Expired Transients:

DELETE FROM wp_options WHERE option_name LIKE ('_transient_timeout%') OR option_name LIKE ('_transient_%');

WP-CLI (Best Method):

wp transient delete --all

Part 2: Autoloaded options (the silent performance killer)

The wp_options table contains an autoload column. If set to yes, that option is loaded on every page load. Plugins often leave trash here after uninstallation.

How to diagnose?

Check how much data (in bytes) is being autoloaded:

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

If the result exceeds 800 KB - 1 MB, you have a problem.

How to find the largest options?

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

You will often find old data from cache plugins, builders, or logs here. If the plugin is no longer used, you can safely delete these options or change autoload to no.

Part 3: InnoDB vs MyISAM

In 2026, you should no longer use the MyISAM engine. InnoDB is the standard, offering:

• Row-level locking: MyISAM locks the entire table on write.
• ACID Transactions: Data safety.
• Foreign Keys: Relationship consistency.

Check table engine:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

Convert to InnoDB:

If you find MyISAM tables, convert them:

ALTER TABLE wp_posts ENGINE=InnoDB;
ALTER TABLE wp_comments ENGINE=InnoDB;
ALTER TABLE wp_options ENGINE=InnoDB;
-- and so on for all tables

Part 4: Indexing (advanced)

WordPress usually has good default indexes, but plugins often add their own tables without proper indexes. Missing an index on a WHERE or JOIN query means MySQL must scan every row in the table.

Example: Slow search IN WooCommerce

If you have a store with 100,000 orders, and a plugin searches orders by meta_key without an index, the “My Account” page might take 10 seconds to load.

Solution: Add indexes to columns you frequently search or sort by.

CREATE INDEX idx_meta_key_value ON wp_postmeta (meta_key(191), meta_value(50));

(Note: Indexing meta_value is tricky because it’s TEXT. Avoid searching by meta_value if possible).

Part 5: Prevention (configuring wp-config.php)

Instead of constantly cleaning, it’s better not to litter. Configure WordPress wisely.

Limit revisions

Add to wp-config.php:

// Limit to 5 recent versions
define( 'WP_POST_REVISIONS', 5 );

// Empty trash every 7 days (default is 30)
define( 'EMPTY_TRASH_DAYS', 7 );

// Increase autosave interval (fewer AJAX requests in editor)
define( 'AUTOSAVE_INTERVAL', 300 ); // seconds

Disable file editing

For security and hygiene:

define( 'DISALLOW_FILE_EDIT', true );

Part 6: Monitoring tools (query monitor)

Don’t guess what slows down the site. Install the Query Monitor plugin. After installation, in the admin bar, you will see page generation time and number of SQL queries.

1. Click on statistics in the bar.
2. Go to the “Queries” tab.
3. Sort by “Time”.

If you see a query taking 0.5s or more – that’s your optimization target. It often comes from a poorly written plugin (“Recently Viewed Products”, “Visit Counter”, etc.).

Summary: Optimization checklist

1. Backup: Always backup before working on the database.
2. Engine: Ensure all tables are InnoDB.
3. Autoload: Check autoloaded options size (aim for < 800KB).
4. Cleaning: Remove revisions, spam, and expired transients.
5. Indexes: Check if heavy queries use indexes.
6. Prevention: Configure wp-config.php to limit trash creation.

When your database is lean and fast, PHP uses less memory, and users get content instantly. This is the foundation of modern WordPress.