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.

#What makes a WordPress database slow

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.

#How to clean the WordPress database

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.

#How to delete 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

#How to delete spam and trashed comments

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';

#How to delete expired transients

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

#How to reduce autoloaded options in wp_options

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 check autoload size in MySQL

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 autoloaded 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.

#How to convert MyISAM tables to InnoDB

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.

#How to check which engine your tables use

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

#How to change the table engine with ALTER TABLE

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

#How to index MySQL tables for WordPress speed

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.

#Why WooCommerce order search is slow without an index

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).

#How to stop database bloat in wp-config.php

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

#How to limit revisions, trash and autosave

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

#How to disable the WordPress file editor

For security and hygiene:

define( 'DISALLOW_FILE_EDIT', true );

#How to find slow queries with 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.).

#WordPress database optimization checklist

Learn more about WordPress speed optimization at WPPoland.

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.