Optimize WordPress Database Tables with ANALYZE, OPTIMIZE, and Slow Query Log

WordPress database performance degrades over time as post revisions, transients, and deleted records accumulate in wp_posts, wp_postmeta, and wp_options — leading to table fragmentation, stale index statistics, and slow queries. MySQL provides three maintenance commands that address different aspects of this degradation: ANALYZE TABLE updates the index statistics used by the query optimizer without locking the table (for InnoDB, it samples index pages and updates cardinality estimates); OPTIMIZE TABLE reclaims fragmented space by rebuilding the table and its indexes (equivalent to ALTER TABLE ... ENGINE=InnoDB for InnoDB), which requires a brief table lock in older MySQL versions but uses online DDL in MySQL 5.7+; and CHECK TABLE verifies table integrity and detects corruption without modifying data. Index statistics become stale when a large batch of rows is inserted or deleted — the optimizer may choose a full table scan over an index when cardinality estimates are wrong, and ANALYZE TABLE wp_postmeta after a bulk import corrects this. Fragmentation in wp_posts and wp_options is measured with information_schema.TABLES by comparing DATA_FREE to DATA_LENGTH — a DATA_FREE value above 20% of DATA_LENGTH warrants an OPTIMIZE TABLE. The slow query log is the most actionable tool for finding queries that need index improvements: set slow_query_log = 1, long_query_time = 0.5, and log_queries_not_using_indexes = 1 in my.cnf, then analyze the log with mysqldumpslow -s t -t 20 /var/log/mysql/slow.log to find the 20 slowest query patterns sorted by total execution time. The WP-Cron wp_scheduled_delete event removes expired transients nightly, but auto-draft posts, trash posts older than 30 days, and orphaned wp_postmeta rows (rows whose post_id no longer exists in wp_posts) are not cleaned automatically and must be deleted with targeted SQL. The composite index post covers the EXPLAIN-driven process for adding the right indexes — maintenance with ANALYZE and OPTIMIZE is the complementary step that keeps the optimizer using those indexes correctly.

Problem: A WordPress database that has been running for over a year shows increasing query times despite correct indexes, because post revisions, expired transients, and orphaned meta rows have fragmented tables and caused the query optimizer to use stale cardinality estimates.

Solution: Enable the slow query log to identify the worst queries, clean orphaned rows and post revisions with targeted SQL, run ANALYZE TABLE on all WordPress tables to refresh index statistics, and run OPTIMIZE TABLE on tables with more than 20% fragmentation.

-- ── 1. Check fragmentation for all WordPress tables ─────────────────────────
SELECT
    TABLE_NAME,
    ROUND(DATA_LENGTH  / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND(DATA_FREE    / 1024 / 1024, 2) AS free_mb,
    ROUND(DATA_FREE / (DATA_LENGTH + 1) * 100, 1) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'helloadmin'
  AND ENGINE = 'InnoDB'
ORDER BY DATA_FREE DESC;

-- ── 2. Clean post revisions (keep none — adjust WHERE as needed) ────────
DELETE FROM wp_posts WHERE post_type = 'revision';

-- ── 3. Clean orphaned postmeta rows ─────────────────────────────────────
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

-- ── 4. Delete expired transients ────────────────────────────────────────
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
   OR option_name LIKE '_site_transient_%';

-- ── 5. Run ANALYZE on all WordPress tables ───────────────────────────────
ANALYZE TABLE
    wp_posts, wp_postmeta, wp_options,
    wp_comments, wp_commentmeta, wp_terms,
    wp_term_taxonomy, wp_term_relationships,
    wp_usermeta, wp_users;

-- ── 6. OPTIMIZE tables with > 20% fragmentation ──────────────────────────
-- Run for each fragmented table returned by query 1:
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_options;

# /etc/mysql/mysql.conf.d/slow-query.cnf
# Enable the slow query log to find queries needing index improvements

[mysqld]
slow_query_log             = 1
slow_query_log_file        = /var/log/mysql/slow.log
long_query_time            = 0.5    # log queries taking > 500 ms
log_queries_not_using_indexes = 1   # log full-table-scan queries
min_examined_row_limit     = 100    # skip trivially fast full scans

# Analyze the log after 24 h of traffic:
# mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

NOTE: OPTIMIZE TABLE on InnoDB rebuilds the entire table and temporarily doubles its disk space requirement — ensure the MySQL data directory has at least 2× the current table size in free space before running it on large tables like wp_postmeta. Schedule maintenance during the lowest-traffic window and monitor SHOW PROCESSLIST to confirm no long-running queries are waiting on the table lock.