MySQL INFORMATION_SCHEMA for WordPress Database Health Audits

INFORMATION_SCHEMA is MySQL’s built-in metadata database — it contains tables with real-time information about every table, index, column, and user in your instance. For WordPress database health audits, it provides table sizes, row counts, index usage, and fragmentation data without touching application code.

Problem: A WordPress database is growing but it is unclear which tables are largest, which indexes are unused, and which autoloaded options are bloating every page load — there is no built-in dashboard for database health.

Solution: Query information_schema.TABLES to rank tables by data and index size, information_schema.INNODB_INDEXES to find unused indexes, and wp_options WHERE autoload = 'yes' to audit autoloaded data. Run monthly and track growth trends to catch problems before they affect performance.

The examples below query table sizes, find unused indexes, detect heavily fragmented tables, and build a stored health-check report for the WordPress database.

-- 1. Table sizes — rows, data, index, and overhead (fragmentation)
SELECT
    table_name,
    table_rows                                         AS estimated_rows,
    ROUND(data_length   / 1024 / 1024, 2)             AS data_mb,
    ROUND(index_length  / 1024 / 1024, 2)             AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    ROUND(data_free     / 1024 / 1024, 2)             AS overhead_mb
FROM information_schema.TABLES
WHERE table_schema = 'helloadmin'
ORDER BY (data_length + index_length) DESC;

-- 2. Find the largest postmeta keys (common source of bloat)
SELECT meta_key, COUNT(*) AS cnt, SUM(LENGTH(meta_value)) / 1024 AS size_kb
FROM ynYYAa7bFT_postmeta
GROUP BY meta_key
ORDER BY cnt DESC
LIMIT 20;

-- 3. Identify tables with high fragmentation (data_free > 20% of data_length)
SELECT table_name,
       ROUND(data_free / data_length * 100, 1) AS fragmentation_pct
FROM information_schema.TABLES
WHERE table_schema = 'helloadmin'
  AND data_length > 0
  AND data_free / data_length > 0.2
ORDER BY fragmentation_pct DESC;

Find duplicate and unused indexes, then clean them up:

-- 4. List all indexes in the WordPress database
SELECT table_name, index_name, non_unique,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.STATISTICS
WHERE table_schema = 'helloadmin'
GROUP BY table_name, index_name, non_unique
ORDER BY table_name, index_name;

-- 5. Find duplicate indexes — a prefix index makes a longer index redundant
-- e.g., INDEX(a) is made redundant by INDEX(a, b)
-- Use pt-duplicate-key-checker from Percona Toolkit for automated detection:
-- pt-duplicate-key-checker --host=127.0.0.1 --user=root --databases=helloadmin

-- 6. Count total auto_transient rows (often the biggest wp_options bloat)
SELECT COUNT(*) AS transient_count,
       ROUND(SUM(LENGTH(option_value)) / 1024 / 1024, 2) AS total_mb
FROM ynYYAa7bFT_options
WHERE option_name LIKE '\_transient\_%';

-- 7. Rebuild fragmented tables (locks the table — run during maintenance window)
OPTIMIZE TABLE ynYYAa7bFT_postmeta;
OPTIMIZE TABLE ynYYAa7bFT_options;

-- 8. Check InnoDB buffer pool hit ratio (should be > 99%)
SELECT ROUND(
    (1 - (
        (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') /
        (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    )) * 100, 2
) AS buffer_pool_hit_ratio_pct;

NOTE: Run the table-size query monthly and track the growth of wp_postmeta and wp_options — these two tables are responsible for most WordPress database performance problems. A wp_options table over 10 MB usually contains excessive transient or session data that should be migrated to a dedicated table or external store.