SQL audit snippets for legacy WordPress: a cheatsheet

Inherited project. A WordPress install that has been running since 2011, last touched by a freelancer in 2019, now sitting on a shared host where wp-admin takes nine seconds to render the dashboard. The handover doc says "it's fine, just keep it patched." Nothing about the database. Nothing about the 480MB wp_options table.

We keep a file called wp-audit.sql on every laptop. Eight fragments, all read-only, all safe to run on production with a warm coffee in hand. They answer the three questions a legacy WordPress audit actually starts with: where is the weight, what is orphaned, and what did six different developers leave behind in the schema.

The autoload pile

Every page load on a WordPress site pulls every row from wp_options where autoload = 'yes'. A clean install lands somewhere under 1MB. Sites we audit routinely hit 40MB, 80MB, occasionally 200MB. The cause is almost always a plugin that wrote a giant serialized array and never cleaned it up.

Fragment 1 is the headline number:

SELECT ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS autoload_mb
FROM wp_options
WHERE autoload = 'yes';

If that returns anything over 3, fragment 2 finds the offenders:

SELECT option_name, ROUND(LENGTH(option_value)/1024, 1) AS kb
FROM wp_options
WHERE autoload = 'yes'
ORDER BY LENGTH(option_value) DESC
LIMIT 25;

What turns up: an SEO plugin caching a sitemap blob, a backup plugin storing logs as a single option, a long-uninstalled page builder that left 18MB behind. The wp_load_alloptions documentation is worth re-reading once you see the list, because it explains why a 200KB option costs you on every request, not just the one that wrote it.

Transients that never died

Transients are WordPress's poor-man's cache. They are supposed to expire. They often do not, because the cron job that cleans them stops firing the moment DISABLE_WP_CRON gets set and nobody wires up a real one.

Fragment 3 finds the expired timeouts:

SELECT COUNT(*) AS dead_transients
FROM wp_options
WHERE option_name LIKE '\_transient\_timeout\_%' ESCAPE '\\'
  AND option_value < UNIX_TIMESTAMP();

On one site (a Magento storefront with WordPress bolted on for the blog) this returned 412,000. Each row was tiny on its own. Together they were dragging every admin query through a fat table scan. The Transients API page covers the lifecycle if you need to remind yourself why the cleanup never ran.

Revisions, the silent gainer

WordPress keeps post revisions forever unless you set WP_POST_REVISIONS. On a site with 12,000 published posts we routinely see 180,000 revision rows.

Fragment 4 ranks them:

SELECT post_parent, COUNT(*) AS revisions
FROM wp_posts
WHERE post_type = 'revision'
GROUP BY post_parent
ORDER BY revisions DESC
LIMIT 20;

One post will usually have 600+. It is always a homepage that an editor kept tweaking for two years. Worth knowing before you propose a cleanup, because the editor may want those back.

Orphans nobody noticed

Plugins get uninstalled. The rows they wrote into wp_postmeta and wp_usermeta do not. After a decade of churn, the orphan count can outweigh the live rows.

Fragment 5, orphaned postmeta:

SELECT COUNT(*) AS orphan_postmeta
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

Fragment 6, orphaned usermeta:

SELECT COUNT(*) AS orphan_usermeta
FROM wp_usermeta um
LEFT JOIN wp_users u ON u.ID = um.user_id
WHERE u.ID IS NULL;

If either returns a six-figure number, you have found a chunk of your slow queries. A Dutch agency we work with handed us a site with 2.1M orphan postmeta rows from a CRM plugin removed in 2017. Deleting them dropped the post-edit screen from 7 seconds to 1.4.

Schema drift

Two things go wrong over years of migrations: tables end up on different storage engines, and they end up on different charsets. Either one will surprise you later.

Fragment 7 lists non-InnoDB tables:

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND engine <> 'InnoDB';

MyISAM tables in a WordPress install in 2026 are a tell. Usually it is a custom analytics table from 2014 that nobody migrated. They do not support foreign keys, they do not recover cleanly from a crash, and they lock the whole table on writes. MySQL's own docs have made the case for years.

Fragment 8 catches collation drift:

SELECT table_collation, COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema = DATABASE()
GROUP BY table_collation;

If that returns more than one row, you have tables in utf8mb4_unicode_ci and tables still in utf8_general_ci. JOINs across them trigger implicit conversions and quietly bypass your indexes. Worth fixing before you ship the next feature, not after.

Running these without scaring production

All eight fragments are read-only. None of them write, lock, or block. They are still real queries against a real database, so:

• Run them on a read replica if you have one. If you do not, run them during a quiet hour.
• The information_schema queries can be slow on shared hosting where the metadata is enormous. Do not be surprised if fragment 7 takes 30 seconds.
• Keep the file in version control. The fragments evolve as you find new failure modes on new sites.

When we built Pier we ran into the same loop on almost every legacy site we touched: open the database, run these queries, copy the rows into a doc, decide what to delete. Pier's MySQL editor ships a chat layer over these snippets so you can ask "what is eating my autoload" in plain English, get the fragment back with the rows, and keep a version history entry on anything you act on.

The smallest thing to do today: copy the autoload byte-count query into a file called wp-audit.sql and run it against the next legacy WordPress site that lands on your desk. Whatever number it returns will tell you how much of the rest of the file you need.