WordPress MySQL queries: a legacy maintainer's cheatsheet

A client emails at 17:40 on a Tuesday. They updated WooCommerce on a 2018 build, and now wp-admin returns a white screen. The error log is empty. SSH gives you a shell, but only to public_html. You have phpMyAdmin, ten minutes, and the agency lead is already asking questions in a different channel. The only tool that's going to fix this in time is the MySQL console.

This is the situation where the MySQL queries you actually memorised earn their keep. Not the elegant statements from a database design course. The half-dozen one-liners that fix things on a legacy WordPress site running 5.8 on PHP 7.4 behind a Litespeed cache, with seven years of post revisions and a postmeta table the size of a small lake.

What follows is the working set. Run them on a copy first if the table size makes you nervous, and back up the row you are about to modify. Always.

The site-URL pair

After any migration, two rows in wp_options decide whether the site loads at all.

SELECT option_id, option_name, option_value
FROM wp_options
WHERE option_name IN ('siteurl', 'home');

Updating them looks trivial:

UPDATE wp_options
SET option_value = 'https://new.example.com'
WHERE option_name IN ('siteurl', 'home');

It is not. This fixes the front page and the login redirect. It does not fix the URLs hard-coded into post content, widget JSON, Customizer mods, or serialized plugin settings. WordPress stores a lot of state as PHP-serialized strings, and a serialized string includes its byte length. A naive SQL search-and-replace breaks the length prefix and silently corrupts the row.

For the rest of the URLs, use WP-CLI's search-replace command, which deserialises before substituting:

wp search-replace 'https://old.example.com' 'https://new.example.com' \
  --skip-columns=guid --all-tables-with-prefix

If WP-CLI is not installed and the host won't let you install it, the interconnect/it Search Replace DB script is the safe fallback. Never run a raw UPDATE ... SET ... REPLACE() across all tables. Once a week, somewhere, someone does that and spends Saturday restoring from backup.

The locked-admin escape hatch

When a plugin update breaks the admin and you cannot reach /wp-admin to deactivate anything, the brute-force fix is to empty the active plugins list straight from the database.

SELECT option_value
FROM wp_options
WHERE option_name = 'active_plugins'\G

Copy that string somewhere safe. Then:

UPDATE wp_options
SET option_value = 'a:0:{}'
WHERE option_name = 'active_plugins';

The value a:0:{} is a PHP-serialized empty array. The site loads with zero plugins, you log in, you reactivate them one by one until you find the offender. Paste the saved value back when you are done, minus the broken plugin.

The autoload audit

Every row in wp_options with autoload = 'yes' loads on every single request. On a healthy site that's around 200KB. On a five-year-old WordPress with a graveyard of deactivated plugins, it can be 8MB, and your TTFB is mostly MySQL reading option blobs.

SELECT option_name, LENGTH(option_value) AS size_bytes
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size_bytes DESC
LIMIT 20;

And the headline number:

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

Common offenders we see on legacy WordPress sites: rank_math_404_logs, jetpack_sync_*, woocommerce_admin_notes, old elementor_* revision caches, and any option ending in _log or _queue. Most have no reason to autoload. Switch them off:

UPDATE wp_options
SET autoload = 'no'
WHERE option_name = 'rank_math_404_logs';

If the plugin actually needs the option, it will call get_option() on demand. The cost moves from every request to the few requests that need it.

The revisions purge

Post revisions are useful until they aren't. An editorial site that has been live since 2018 with three authors and unlimited revisions has, in our experience, somewhere between 60 and 85 percent of wp_posts taken up by revision rows. Count them:

SELECT post_status, post_type, COUNT(*) AS n
FROM wp_posts
GROUP BY post_status, post_type
ORDER BY n DESC;

Delete revisions older than 90 days:

DELETE FROM wp_posts
WHERE post_type = 'revision'
  AND post_modified < DATE_SUB(NOW(), INTERVAL 90 DAY);

That leaves orphaned wp_postmeta rows pointing at posts that no longer exist. Clean them up:

DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

Then cap future revisions in wp-config.php so the table doesn't grow back:

define( 'WP_POST_REVISIONS', 5 );

Password resets, role lookups, transient sweeps

Three small MySQL queries that get used more than the headline ones.

Reset an admin password without email access:

UPDATE wp_users
SET user_pass = MD5('temporary-pw-change-me')
WHERE user_login = 'admin';

WordPress accepts plain MD5 hashes on login and silently re-hashes to phpass on success. This is documented behaviour, not a backdoor. Change the password through the admin UI immediately after.

List every administrator-level user on a site you have just inherited:

SELECT u.ID, u.user_login, u.user_email, u.user_registered
FROM wp_users u
JOIN wp_usermeta m ON u.ID = m.user_id
WHERE m.meta_key = 'wp_capabilities'
  AND m.meta_value LIKE '%administrator%'
ORDER BY u.user_registered;

If there is an admin account you do not recognise, especially one registered between 02:00 and 05:00 UTC, you are doing security work now, not maintenance.

Sweep expired transients (most plugins never clean theirs):

DELETE FROM wp_options
WHERE option_name LIKE '\_transient\_%'
   OR option_name LIKE '\_site\_transient\_%';

Find the largest tables in the database, using information_schema:

SELECT table_name,
       ROUND((data_length + index_length)/1024/1024, 1) AS mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY mb DESC
LIMIT 10;

Nine times out of ten the answer is wp_postmeta, wp_options, or wp_actionscheduler_logs. The tenth time it's a plugin you have never heard of writing every page view into a table with no index, and the conversation with the client about whether to keep that plugin starts there.

Keeping the cheatsheet honest

None of these MySQL queries are clever. They are the ones you reach for when a legacy site is on fire and the only tool you trust is the one that was already on the box when the site went live in 2014. The skill is knowing which one to run and in what order, not memorising obscure JOIN tricks.

When we built Pier we ran into this exact thing with our own customers: people running these queries from phpMyAdmin and praying they had typed the WHERE clause correctly. The way we ended up handling it was to wrap every UPDATE and DELETE in the built-in MySQL editor with a row-level preview, and to keep a version history snapshot of every row touched, so a wrong WHERE clause is a two-click revert instead of a Saturday restoring backups.

The smallest thing you could do today: open the database on the legacy site you maintain most, run the autoload audit query, and screenshot the top five rows. You will almost always find a plugin you had forgotten about quietly leaking telemetry into option storage on every request.