How to Clean Up your WordPress Database

When you uninstall a WordPress plugin, it often leaves behind entries in your database, taking up space and potentially slowing down your site.

Manually cleaning these can improve performance and keep your database tidy.

The following SQL commands work as long as your WordPress database prefix is the default one.

If you change your database table prefix, you still can use them but you need to make some adjustments to it.

Make sure to make a back up of your site and database before making these changes

---

---

WordPress Database Tables

If you want to make changes to your database, you should use Adminer or PHPMyAdmin.

Adminer and PHPMyAdmin are both web-based tools used to manage MySQL or MariaDB databases, commonly used for WordPress and other PHP-based applications.

Here’s the list of default WordPress tables:

1. wp_options
2. wp_posts
3. wp_postmeta
4. wp_terms
5. wp_termmeta
6. wp_term_taxonomy
7. wp_term_relationships
8. wp_users
9. wp_usermeta
10. wp_comments
11. wp_commentmeta

These WordPress tables and a store data related to users on the website, such as login credentials, roles, and custom user information.

#8	wp_users
#9	wp_usermeta

There is nothing that can’t be optimized on the wp_user table and the wp_usermeta that you could analyze and see if you need it.

You might find meta_keys values like these, values that might be removed once the plugins that created were removed.

• edit_seopress_404_per_page
• closedpostboxes_adsforwp
• closedpostboxes_advanced_ads

---

If you haven’t enabled comments and deleted the ones that were once submitted, don’t expect to find any rows on these tables

#10	wp_comments
#11	wp_commentmeta

---

Remove Revisions from Post Table

This is the SQL command to preview all the revision stored on this table

SELECT * FROM wp_posts WHERE post_type = 'revision';

and this what you need to remove all post revisions.

DELETE FROM wp_posts WHERE post_type = 'revision';

---

Removing Footnotes

For some reason, WordPress created a custom field for footnotes so every time you create a post or update it creates a row on your database.

You can remove all those footnotes by using this SQL command:

DELETE FROM wp_postmeta
WHERE meta_key = 'footnotes';

---

Removing Entries Created by WordPress in Post_Meta

In case you want to remove “edit_last” entries, you can preview what you are about to delete using this command

SELECT FROM wp_postmeta
WHERE meta_key = '_edit_last';

and you can get rid of those this way

DELETE FROM wp_postmeta
WHERE meta_key = '_edit_last';

Just change the meta key value to remove all edit_lock entries

DELETE FROM wp_postmeta
WHERE meta_key = '_edit_lock';

---

Removing Entries Left Behind by Plugins in Post_Meta

If you don’t want to be that specific, you can remove entries that contain certain keyword.

Preview what you want to delete using this command

SELECT * FROM wp_postmeta
WHERE meta_key LIKE '%itsec%';

and get rid of those entries this way

DELETE FROM wp_postmeta
WHERE meta_key LIKE '%itsec%';

Let’s say that you wanna delete all traces of SEOPress from your post_meta table:

DELETE FROM wp_postmeta
WHERE meta_key LIKE '%seopress%';

In case you don’t want to run command one by one to delete unwanted entries, you can use this to select entries

SELECT * FROM wp_postmeta
WHERE meta_key LIKE '%seopress%'
   OR meta_key LIKE '%rank_math%'
   OR meta_key LIKE '%yoast%';

And remove all those entries this way:

DELETE FROM wp_postmeta
WHERE meta_key LIKE '%seopress%'
   OR meta_key LIKE '%rank_math%'
   OR meta_key LIKE '%yoast%';

Sometimes you delete posts but for some reason, data from those posts doesn’t get deleted, with this SQL command you will preview orphaned date

SELECT * FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);

If you feel like deleting orphaned data, run this command and it will be gone.

DELETE FROM wp_postmeta 
WHERE post_id NOT IN (SELECT ID FROM wp_posts);

---

Removing Entries from WP_Options

In case you want to remove entries by left by plugins on wp_options, you could identify the keywords of such plugins and do it like this

DELETE FROM wp_options
WHERE option_name LIKE '%ad_inserter%'
   OR option_name LIKE '%advanced-ads%'
   OR option_name LIKE '%ai1wm%'
   OR option_name LIKE '%generateblocks%';

I removed entries left behind by ad Inserter, Advanced Ads, All in One WP Migration and GenerateBlocks

You can always preview what you are going to delete by previewing it

SELECT * FROM wp_options
WHERE option_name LIKE '%ad_inserter%'
   OR option_name LIKE '%advanced-ads%'
   OR option_name LIKE '%ai1wm%'
   OR option_name LIKE '%generateblocks%';

If you don’t want to dig keywords from wp_options, Advanced Database Cleaner Pro does a great job identifying orphan entries from this table.

---

Removing Entries from wp_usermeta

In case you want to remove entries from wp_user, you can find some keyword and remove all entrie sby running this command.

DELETE FROM wp_usermeta
WHERE meta_key LIKE '%wp_googlesitekit%'
   OR meta_key LIKE '%rocket_%'
   OR meta_key LIKE '%advanced-ads%';

---