How to Clean Metadata: Simplifying WordPress Database Cleanup

Metadata are little bits of basic information or data, providing information about your website to help identify and describe specific items you have on your website, such as image attributes, making your website more SEO-friendly.

From time to time, optimizing the database to improve site performance for your visitors is essential. This article will explain how to clean WordPress metadata.

• What You Need
• How to Open the Database in phpMyAdmin
• How to Remove Rows With No ID
• How to Optimize the Post and Comment Tables
• Summary

What You Need

To complete this tutorial, you'll need to know the following information:

• Before you get started:

  Important: Back up your database before performing any of the steps in this article. You'll be deleting data from the database. Accidentally deleting the wrong information may require restoring your database from a backup. If you need help with how to backup your database, please see our article on how to backup a MySQL database.

• The username and password to your Bluehost account.
   
  Need help logging in? If you need help or a refresher, this guide will show you How To Login To WordPress Sites - WP Admin Access.


• The name of your WordPress database. If you don't have this, please see our article on  how to find your WordPress database.

How to Open The Database in phpMyAdmin

1. Log in to your Bluehost Account Manager.
2. Click the Hosting tab from the side navigation menu to the left.
3. Scroll down to click the CPANEL button under Quick Links.
4. Under Databases, click phpMyAdmin
5. Log in with your cPanel username and password (if prompted). 
6. Along the left-hand side of the screen, click on the database associated with your WordPress installation.
   

How to Remove Rows With No ID

1. Note the prefix attached to your database tables. In our example database, all table names begin with wp_rfrz_. This is our database prefix; yours will differ.
   
2. Click on the SQL tab.
   

Comment Metadata

These steps explain how to clear the comment metadata.

1. Copy and paste this code into the textbox:

   SELECT * FROM your_prefixcommentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM your_prefix_comments );
   DELETE FROM your_prefix_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM your_prefix_comments );
   SELECT * FROM your_prefix_commentmeta WHERE meta_key LIKE '%akismet%'; 
   DELETE FROM your_prefix_commentmeta WHERE meta_key LIKE '%akismet%';
   

2. Replace your_prefix_ with your database prefix.
   
3. Click the Go button.

Post Metadata

These steps explain how to clear the post metadata.

1. Copy and paste this code into the textbox:

   SELECT * FROM your_prefix_postmeta pm LEFT JOIN your_prefix_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
   DELETE pm FROM wp_postmeta pm LEFT JOIN your_prefix__posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
   

2. Replace your_prefix_ with your database prefix.
3. Click the Go button.

How to Optimize The Post and Comment Tables

1. At the top of phpMyAdmin, click the Structure tab.
   
2. Select the comments, commentmeta, posts, and postmeta tables.
   
3. In the drop-down menu labeled With Selected, choose Optimize Table.

The tables should now have unnecessary metadata removed. If you encounter any issues with the website after this procedure, consider restoring your database using your backup.

Summary

This guide outlines the steps for cleaning metadata in WordPress to enhance site performance and SEO. It requires backing up the database before proceeding to prevent data loss. Key steps include accessing the database via phpMyAdmin, removing rows without an ID, and optimizing post and comment tables. Users need their Bluehost login credentials, WordPress database name, and knowledge of database prefixes to execute these steps effectively. The process involves executing SQL queries to delete unnecessary metadata and optimizing tables to improve website efficiency. Please view this Bluehost blog for everything you need to know about WordPress SEO.