I have a client who hired me after his website had been crashing for months. This week, I emptied 285 megabytes of transients from his wp_options table.
A deleted social media plugin had left thousands of rows of transients in the database. Because the plugin was gone and didn't clean up after itself, the transients were in database purgatory—they couldn't expire, so a plugin like WP-Optimize couldn't see and clear them. So they sat there for probably years, crashing this website over and over.
After I deleted them, the wp_options table went from 285 MB to 2. Seriously.
Don't let this happen to your WordPress website.
Here is an extensive list of MySQL CLI snippets to inspect, diagnose, and clean up your WordPress database. You can also use this SQL code inside phpMyAdmin.
Key things to watch for:
Spring cleaning: you can fix these with a plugin such as WP-Optimize or clean them by hand.
- Revision size in the
wp_poststable - Spam and trashed comments in
wp_comments
Advanced cleaning:
- The
wp_optionstable loads on every WordPress page and is crucial to optimize—aim for under 2 MB and 500 rows. - Transient size in the
wp_optionstable - Orphaned autoload options in
wp_options - Funny business or orphaned data in
wp_postmeta—like a plugin taking up 100MB duplicating your post content. Yes, I've seen that, too. - Large tables with unnecessary plugin logs, such as EWWW or WordFence
Check the size of the database, using your own 'database_name'.
SELECT (SUM(DATA_LENGTH + INDEX_LENGTH))/1048567 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name';
Check an individual table size:
SELECT (SUM(DATA_LENGTH + INDEX_LENGTH))/1048567 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wp_posts';
Not clean-up, just a step I take in evaluating a database.
Count the rows—always do this first before dumping 10,000 rows into your CLI.
SELECT COUNT(*) FROM wp_users;
Check the user list
SELECT * FROM wp_users;
Security: check for the “admin” user. Use something else for your site admin as this is a default and thus a hacker target.
SELECT * FROM wp_users
WHERE user_login = 'admin';
Check for orphaned metadata by listing usermeta user IDs not found in users:
SELECT DISTINCT user_id
FROM wp_usermeta
LEFT JOIN
wp_users
ON wp_users.id = wp_usermeta.user_id
WHERE wp_users.id IS NULL;
Check the list of meta keys for plugin additions - WordPress uses 20+ by default.
SELECT DISTINCT meta_key FROM wp_usermeta;
Security: check who the admins are
SELECT *
FROM wp_usermeta;
WHERE meta_value LIKE ('%administrator%');
Or search for all user roles:
SELECT *
FROM wp_usermeta
WHERE `meta_key` = 'wp_capabilities'
OR `meta_key` = 'wp_user_level'
ORDER BY user_id;
Check post type, data size, and row counts. This is a good way to check for table clutter from post revisions.
SELECT
post_type,
COUNT(*) AS `Rows`,
ROUND(
SUM(
LENGTH(
CONCAT(
ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_parent, guid, menu_order, post_type
)
)
)/1048567, 2
) AS `Data_in_MB`
FROM wp_posts
GROUP BY post_type
ORDER BY `Data_in_MB` DESC;
Delete all revisions
DELETE FROM wp_posts WHERE post_type = 'revision';
Check wp_postmeta keys, rows, and data size. This will give you a grouped, ranked list of the meta keys, which you can ID to the plugins that generated them with their naming prefix.
SELECT
meta_key,
COUNT(*) AS `Rows`,
ROUND(
SUM(
LENGTH(
CONCAT(meta_id, post_id, meta_key, meta_value)
)
)/1048567, 2
) AS `Size_in_MB`
FROM wp_postmeta
GROUP BY meta_key
ORDER BY `Size_in_MB` DESC;
See also this in-depth article on digging through meta keys.
Get table size
SELECT
ROUND(
SUM(
LENGTH(
CONCAT(option_id, option_name, option_value, autoload)
)
)/1048567, 2
) AS `Options_Table_Size_In_MB`
FROM wp_options;
Get transient size and row count
SELECT
COUNT(*) AS `Transients_Rows`,
ROUND(
SUM(
LENGTH(
CONCAT(option_id, option_name, option_value, autoload)
)
)/1048567, 2
) AS `Transients_data_in_MB`
FROM wp_options
WHERE option_name LIKE ('%\_transient\_%');
Check how many transients autoload
SELECT COUNT(*) AS `Transient_Rows_That_Autoload`
FROM wp_options
WHERE option_name LIKE ('%\_transient\_%') AND autoload = 'yes';
Delete all transients:
You might want to delete by plugin namespace or a date window instead of deleting all of them, especially if you have current e-commerce or logged-in user sessions running via this method.
If you're using an object cache plugin such as Memcached or Redis, you might not have transients in your database at all. Congrats!
DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%');
Get the size and row count of options which autoload
SELECT
COUNT(*) AS `Autoload_Row_Count`,
ROUND(
SUM(
LENGTH(
CONCAT(option_id, option_name, option_value, autoload)
)
)/1048567, 2
) AS `Autoload_Data_in_MB`
FROM wp_options
WHERE autoload = 'yes';
See all autoload options:
This is a good method to check for orphaned options still loading on your site every time even after you deleted the plugin that created them. I guarantee you have at least a dozen.
SELECT option_name FROM wp_options WHERE autoload = 'yes';