Home / Know-how
Know-how

WordPress Database Optimization — Cleanup, Indexes, and SQL Query Performance

Published: March 20, 2026 · Author: Marcin Szewczyk-Wilgan

The MySQL database is the heart of every WordPress site — it stores content, settings, users, orders, and plugin configurations. Over time the database grows: post revisions, expired transients, orphaned meta data from removed plugins, logs, and sessions. A site that once loaded in one second starts needing three — not because there is more content, but because the database is cluttered with data nobody needs. In this article, we explain where WordPress database performance problems come from, how to diagnose them, and how to safely optimize — from cleanup through indexes to MySQL configuration.

Where WordPress Database Problems Come From

WordPress stores enormous amounts of data in a few key tables. The problem appears when these tables grow uncontrollably:

wp_postmetaThe largest table in most WordPress installations. Stores all post, page, and WooCommerce product meta data in a key-value format (EAV). A store with 5,000 products and variants can have millions of rows. Orphaned meta data from removed plugins remains forever — unless you clean it.
wp_options (autoload)The wp_options table contains WordPress and plugin settings. Options with the autoload=yes flag are loaded on EVERY HTTP request — even if they are not needed. Plugins often save large data with autoload=yes. When the total autoload size exceeds 1 MB, every request starts by loading a megabyte of unnecessary data.
Post revisionsWordPress saves an unlimited number of revisions for every post by default. An article edited 50 times has 50 revisions in wp_posts — plus associated meta data in wp_postmeta. With 500 posts, that is 25,000 unnecessary rows. Set WP_POST_REVISIONS in wp-config.php (we recommend 3–5).
TransientsTransients are temporary cache data stored in wp_options. They should auto-delete on expiration — but often they do not. Thousands of expired transients clutter wp_options and slow down queries. Without object cache (Redis), transients are the only cache mechanism — and simultaneously the problem.
WooCommerceCustomer sessions, logs, expired webhooks, Analytics data — WooCommerce generates an enormous amount of operational data. The wp_wc_sessions table grows quickly with heavy traffic. WooCommerce logs older than 30 days have no operational value and should be cleaned.

Safe Database Cleanup

Database optimization starts with removing unnecessary data. Precede every operation with a full database backup — mysqldump is your friend.

Expired transientsWP-CLI: wp transient delete --expired. Removes transients whose expiration time has passed. Safe operation — transients will be regenerated on the next request. With object cache (Redis), transients are in memory, not the database — the problem disappears.
RevisionsWP-CLI: wp post delete $(wp post list --post_type='revision' --format=ids) — deletes all revisions. Add define('WP_POST_REVISIONS', 5); to wp-config.php to limit future revisions to 5 per post. Revisions are only valuable for a few days after editing.
Orphaned meta dataMeta data in wp_postmeta, wp_commentmeta, and wp_usermeta referencing non-existent objects (deleted posts, comments, users). SQL query: DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts). Always test on staging before running on production.
Autoload in wp_optionsDiagnosis: SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload = 'yes'; — a result above 1 MB signals a problem. Identify the largest entries and change autoload to 'no' for options not needed on every request. Test after each change.
OPTIMIZE TABLEAfter mass data deletion, InnoDB tables may have “holes” — they occupy more space than needed. OPTIMIZE TABLE wp_postmeta; defragments the table and reclaims space. Run once per quarter. The operation locks the table — execute during low-traffic hours.

Indexes and MySQL Configuration

Data cleanup is half the battle. The other half is optimizing how MySQL executes queries:

Indexes in wp_postmetaThe default index on meta_key in wp_postmeta is of type KEY, not UNIQUE — and covers only the first 191 characters. For WooCommerce stores with millions of rows — an additional index on (post_id, meta_key) or (meta_key, meta_value) can speed up queries several times over. Analyze slow queries with EXPLAIN.
innodb_buffer_pool_sizeThe most important MySQL configuration parameter for WordPress. Determines how much RAM MySQL reserves for caching data and indexes. Recommended: 50–70% of available RAM on the database server. The default value (128 MB) is insufficient for any WordPress installation with traffic.
Slow query logEnable slow query logging (slow_query_log = 1, long_query_time = 1). Analyze logs — find queries taking over 1 second, identify missing indexes, optimize or rewrite problematic queries. This is the fundamental diagnostic tool.
Query MonitorThe Query Monitor plugin shows all SQL queries executed on a given page, their time, and source (which plugin generates them). An invaluable diagnostic tool — it lets you identify plugins generating excessive queries. Use on staging, not production.

Summary

WordPress database optimization is not a one-time operation — it is part of regular maintenance. Cleaning transients and revisions, autoload audits, table optimization, monitoring slow queries — these actions keep the database in shape and prevent gradual performance degradation. For WooCommerce stores with thousands of products and orders — database optimization is one of the most effective investments in speed.

At WebOptimo, database optimization is a standard part of WordPress care plans. We regularly clean, index, and monitor our clients’ databases. If your site is slowing down — contact us or check our WordPress optimization offer.

Frequently Asked Questions About WordPress Database

Post revisions, expired transients, orphaned meta data, WooCommerce logs, user sessions, comment spam, and an overloaded wp_options table with autoload options.

It stores post and product meta data in a key-value format. With thousands of products, it can have millions of rows. Missing indexes and orphaned records dramatically slow down queries.

Always start with a full backup. Delete expired transients, limit revisions, clean spam, remove orphaned meta data, optimize tables. Use WP-CLI or trusted tools.

Options with autoload=yes load on every HTTP request. When their combined size exceeds 1 MB, every request starts by loading a megabyte of unnecessary data from the database.

Transient and revision cleanup: monthly. OPTIMIZE TABLE: quarterly. Autoload audit: after plugin changes. WooCommerce: clean sessions and logs weekly.

Contact

Let’s Talk About Your WordPress Site Performance

We will optimize your database, clean unnecessary data, and improve query performance. No commitments — a concrete proposal after analysis.

Phone

+48 608 271 665

Mon–Fri, 8:00–16:00 CET

E-mail

contact@weboptimo.pl

We respond within 24h

Company

WebOptimo

VAT ID: PL6391758393