Month: May 2023

Last updated on Apr 6th, 2023 | 14 min

As your website grows and you create more content, your WordPress database can become cluttered with unnecessary data, which can slow down your site and create security risks.

In this guide, we will discuss the best WordPress database cleanup methods and plugins to boost site speed and performance.

• Why WordPress database cleanup is necessary
• 8 manual cleanup techniques for a faster WordPress database (via phpMyAdmin
• 5 best plugins for WordPress database cleanup
• Tips for Maintaining a clean and fast database
• Frequently Asked Questions

Why WordPress database cleanup is necessary

Your WordPress database stores all your website data, including posts, pages, comments, media files, and user information. As your website grows, your database can become bloated with unnecessary data, which can:

• Lead to slower page load times;
• Use up more server resources;
• Slow down backups and maintenance tasks;
• Impact caching effectiveness;
• Slow down search functionality.

Maintaining a clean WordPress database is an effective step not only toward better website speed and performance but also:

• Improved website security
• Improved website backup and recovery
• Reduced disk space usage
• Less strain on your servers

8 manual cleanup techniques for a faster WordPress database (via phpMyAdmin)

Before performing any database cleanup, it’s essential to perform a backup. This ensures you have a copy of your website’s data in case anything goes wrong during the cleanup process.

To create a backup, you can use a plugin or access cPanel and use the built-in backup tool. It’s important to save the backup in a secure location (like a Cloud space) so you can restore your website easily.

Generally, the manual approach requires access to your WordPress dashboard and phpMyAdmin – a web-based application for managing MySQL databases.

To access the phpMyAdmin interface, log in from your web hosting control panel or through a plugin like WP phpMyAdmin. Please note all SQL commands shared below use the standard “wp_” prefix. Change it to match the ones used by your database.

Note: Manual WordPress database cleanup requires some technical knowledge. If you don’t feel confident running the command line, we recommend you go with any of the tried-and-tested plugins. Jump to the list.

1. Optimize database tables

In general, you can optimize all database tables, but some may require more attention than others. Here are some tips on how to identify the tables that need optimization:

• Look for tables that are frequently updated: Tables, such as the posts, comments, and user meta tables, are good candidates for optimization.
• Check for tables with large sizes: Tables, such as the wp_options and wp_postmeta tables, may benefit from optimization to improve website performance.
• Identify tables with overhead: Overhead is the amount of space in a table that is used but not required. Tables with a high amount of overhead may need optimization.

To optimize tables, select them from the list on the left in phpMyAdmin, and check the box next to them.

From the “With selected” drop-down menu, select “Optimize table.” Click on the “Go” button to start the optimization process.

2. Delete unused data

Unused data can accumulate over time, leading to a cluttered database. This includes unused themes and plugins, media files, posts and pages, and tags.

Unused Themes and Plugins
To delete inactive WordPress themes and plugins, run the following SQL commands in phpMyAdmin:

For unused themes:

DELETE FROM wp_options WHERE option_name LIKE ‘template_%’ OR option_name LIKE ‘stylesheet_%’;

For unused plugins:

DELETE FROM wp_options WHERE option_name = ‘active_plugins’;

Alternatively, log in to your WordPress dashboard and select the “Appearance” or “Plugins” options for inactive themes and plugins, respectively.

From there, you can select and delete them (in the example below, we’re deleting an inactive WordPress theme).

To delete inactive WordPress plugins completely, we suggest you follow our latest step-by-step guide.

Unused Media Files
To delete unused media files:

1. Log into phpMyAdmin and select your WordPress database from the list of databases.
2. Click on the “wp_posts” table to open it.
3. Look for rows with a “post_type” value of “attachment.” These are media files.
   
   
    
4. To find unused media files, look for rows where the “post_parent” column is set to a value of “0”. This means the media file is not attached to any post or page on your site.
5. Delete the row associated with the file.

To remove unused media files in one hit, use the following command in the SQL tab:

DELETE FROM wp_posts WHERE post_type = ‘attachment’ AND post_parent = 0;

Unused Posts and Pages

To delete unused posts and pages via phpMyAdmin, you can use the following SQL commands:

1. For unused posts:DELETE FROM wp_posts WHERE post_type = ‘post’ AND post_status = ‘draft’;
2. For unused pages:

         DELETE FROM wp_posts WHERE post_type = ‘page’ AND post_status = ‘draft’;

Alternatively, you can delete unused posts and pages inside the WordPress dashboard in a few simple steps:

1. Log in to your WordPress dashboard.
2. Go to the “Posts” or “Pages” section, depending on which content type you want to remove.
3. Look for any posts or pages that you no longer need and want to delete and click on the checkbox next to them
4. Select “Move to Trash” from the “Bulk Actions” drop-down menu and click on the “Apply” button
5. To permanently delete the posts or pages, go to the “Trash” section and perform a bulk action “Delete Permanently,” and confirm with “Apply.”

Unused Tags
To remove unused tags in phpMyAdmin, you can use a combination of SQL queries.

Run the following query to identify all tags that are not associated with any posts or content:

SELECT * FROM wp_terms AS t 
LEFT JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id 
WHERE tt.taxonomy = ‘post_tag’ 
AND tt.count = 0;

This query will list all the tags that are not associated with any posts or content. Make sure that the list contains only the tags you want to delete.

To delete these tags run the following query:

DELETE FROM wp_terms WHERE term_id IN (
  SELECT term_id FROM wp_term_taxonomy WHERE count = 0
);

This query will delete all the tags that have a count of zero, i.e., those that are not associated with any posts or content.

Removing unused tags is also done through your WordPress dashboard.

1. Go to the “Posts” section and click “Tags” from the menu on the left-hand side.
2. Look for any tags that you no longer need and click on the checkbox next to the tag that you want to delete.
3. Select “Delete” from the “Bulk Actions” drop-down menu.
4. Click the “Apply” button to delete the selected tag.

If the tag you want to delete is still associated with any posts, you will need to remove it from those posts first. To do this: 

1. Click on the tag you want to remove. 
2. Check the list of posts that use the tag.
3. Click on each post that uses the tag and remove the tag by clicking on the “X.” 
    

Once the tag is removed from all the associated posts, go back to the “Tags” section and repeat the steps from above.

3. Remove spam comments

In phpMyAdmin, run the following query to identify all comments that have been marked as spam:

SELECT * FROM wp_comments WHERE comment_approved = ‘spam’;

Once you have confirmed the list, you can delete these comments by running the following query:

DELETE FROM wp_comments WHERE comment_approved = ‘spam’;

To remove spam comments via the WordPress dashboard, follow these steps:

1. Go to the “Comments” section and select “Spam” from the “All Comments” drop-down menu.
2. Look for any spam comments you want to delete and click on the checkbox next to them.
3. Select “Delete Permanently” from the “Bulk Actions” drop-down menu.
4. Click on the “Apply” button to confirm the removal.

4. Remove unapproved comments

Similarly to the spam comments, run the following query to identify all comments that have not been approved:

SELECT * FROM wp_comments WHERE comment_approved = 0;

Then, you can delete these comments by running the following query:

DELETE FROM wp_comments WHERE comment_approved = 0;

Alternatively, navigate to your WordPress dashboard. In the “Comments” section, select “Pending” or “Unapproved” from the “All Comments” drop-down menu.

1. Look for any unapproved comments you want to delete and click on the checkbox.
2. Select “Move to Trash” from the “Bulk Actions” drop-down menu.
3. Click the “Apply” button to move the selected unapproved comments to the trash.

To permanently delete the unapproved comments, go to the “Trash” section and perform the bulk action “Delete Permanently” for all the unapproved comments you want gone.

Click on the “Apply” button to finish the process.

5. Remove post revisions

To delete post revisions, enter the following command in the SQL query box and click Go to execute:

DELETE FROM wp_posts WHERE post_type = “revision”;

For a more granular control, the safest alternative is to use a plugin from our recommendations list.

6. Remove old shortcodes

Identifying old shortcodes in WordPress can be challenging, especially if you have a large number of pages or posts on your site. However, there is a way to identify which shortcodes are old and no longer used on your site:

• Check the theme and plugin documentation: If you are using a theme or a plugin that came with shortcodes, check their documentation to see if any of the shortcodes have been deprecated.

Note: We recommend using a plugin if you have a large number of posts. Going through each post to identify and delete a shortcode can prove more time-consuming than expected.

Once you identify which shortcodes are no longer needed, go to your WordPress dashboard. 

1. Go to the “Pages” or “Posts” section and select the page or post where the old shortcode is used.
2. Switch to the “Text” or “HTML” editor mode for the page or post and search for the old shortcode.
3. Once you have located the old shortcode, delete it and save the changes.
4. Repeat this process for each page or post where the old shortcode is used.

7. Remove pingbacks and trackbacks

Pingbacks and trackbacks are two methods that WordPress uses to notify you when another website links to your content. Both methods are designed to help you manage your incoming links and engage with other bloggers and website owners.

However, they can also be a source of spam and unwanted notifications, which is why it’s important to manage them properly or disable them altogether.

In phpMyAdmin, run the following query to identify all comments that have the comment type of “pingback” or “trackback”:

SELECT * FROM wp_comments WHERE comment_type = ‘pingback’ OR comment_type = ‘trackback’;

Delete these comments by running the following query:

DELETE FROM wp_comments WHERE comment_type = ‘pingback’ OR comment_type = ‘trackback’;

You could also use the built-in comment management system in your WordPress dashboard:

1. Click on “Comments” in the left-hand menu.
2. You will see a list of comments, including pingbacks and trackbacks. Check the boxes next to the comments you want to delete.
3. Click the “Bulk Actions” drop-down menu and select “Move to Trash.”
4. Click on the “Apply” button.

8. Remove transients

Transients are temporary pieces of data used to cache data and speed up your website’s performance. However, if not taken care of on a regular basis, they can start harming your speed instead. Here’s how to remove transients in a WordPress database cleanup:

1. via phpMyAdmin
Log into your phpMyAdmin. Then, select your WordPress database and click on the SQL tab. In the SQL window, enter the following command to delete all transients:

DELETE FROM wp_options WHERE option_name LIKE ‘_transient_%’;

2. via WP-CLI
If you’re comfortable using the command line, log in to your server and open a terminal window. Then, navigate to your WordPress directory and run the following command to delete all transients:

wp transient delete –all

Note: It’s important to note that when you remove transients, they will be recreated the next time they are needed. Therefore, it’s a good idea to regularly clean up your database to maintain a fast and efficient website.

5 best plugins for WordPress database cleanup

Advantages of using plugins for database cleanup

Manually cleaning up your database can be a time-consuming and tedious task, especially if you’re not familiar with SQL queries.

Fortunately, there are many plugins available that can automate the process for you. They can help you:

• quickly identify and remove unnecessary data;
• reduce the risk of errors;
• ensure your database is clean and optimized.

Is it safe to use plugins for database cleanup?

Using a plugin for database cleanup is generally safe as long as you choose a reputable plugin and follow best practices. Flip through our list of the best WordPress plugins for database optimization.

1. WP-Optimize

WP-Optimize is a popular plugin that can remove unnecessary data, such as old post revisions, spam comments, and unused tags. It can also optimize your database tables and remove expired transients. One of the standout features of WP-Optimize is the ability to schedule automatic cleanups, so you don’t have to remember to do it manually. The plugin is easy to use and has a user-friendly interface.

Pros:

• Scheduled automatic optimization and cleanup
• Easy to use interface
• Comprehensive cleanup options

Cons:

• Some features are only available in the pro version
• May not be suitable for large databases

2. WP Sweep

WP Sweep can remove unused, orphaned, and duplicated data, as well as optimize your database tables. The plugin is lightweight and easy to use, with a simple interface. WP Sweep also includes a preview function, so you can see what data will be deleted before you confirm the cleanup.

Pros:

• Lightweight and easy to use
• Preview function to see what data will be deleted
• Ability to clean up specific types of data

Cons:

• May not be suitable for large databases
• Some features are only available in the pro version

3. Advanced Database Cleaner

Advanced Database Cleaner comes in both free and paid versions, with the paid version offering more features and functionality. The plugin is designed to help you clean up and optimize your database by removing unnecessary data, such as post revisions, spam comments, and unused tables.

One of the standout features of Advanced Database Cleaner is its ability to schedule automatic cleanups. The plugin also allows you to create custom queries to clean up specific parts of your database.

Pros:

• Free version available
• Schedule automatic cleanups
• Create custom queries for targeted cleaning

Cons:

• Some features only available in the pro version
• Can be more complex to use than some other plugins

4. WP DBManager

WP DBManager offers a range of features, including database backup and optimization options, as well as the ability to repair and restore your database. 

One of the standout features of WP DBManager is its easy-to-use interface, which makes it simple to perform database maintenance tasks. The plugin also includes a range of optimization options to help you speed up your site, such as removing spam comments 
and cleaning up post revisions.

Pros:

• Backup and optimization options
• Easy-to-use interface
• Optimization options to speed up your site

Cons:

• Some users have reported issues with the backup functionality
• Not as customizable as some other plugins
   

5. WP Reset

WP Reset is a powerful WordPress plugin that helps users quickly and easily reset their website to its default settings. Not only can users take a snapshot of their website to quickly restore their website to a specific previous state, but also rely on efficient database cleanup and emergency recovery script.

Pros:

• Easy to use and user-friendly interface perfect for non-technical users
• Time-saving website resetting
• No important data loss during the reset process guaranteed

Cons:

• Some advanced features are only available in the paid version

Best practices for choosing a plugin for WordPress database cleanup

When choosing a plugin for database cleanup, there are a few things to keep in mind.

First, look for a plugin that is regularly updated and has a large user base. This can help ensure that the plugin is compatible with the latest version of WordPress and is free from bugs and security vulnerabilities.

Second, look for a plugin that has a good reputation and positive reviews.

Finally, choose a plugin that meets your specific needs. Some plugins are designed to clean up specific types of data, while others offer more comprehensive cleaning and optimization options.

Tips for maintaining a clean and fast database

Regularly Scheduled Cleanups

One of the best ways to ensure your WordPress database remains clean is to schedule regular cleanups. Depending on the size of your website and how frequently you update content, you may need to schedule cleanups more frequently.

For websites with a lot of traffic and frequent content updates, it’s recommended to schedule cleanups on a weekly or bi-weekly basis. For smaller websites, a monthly or bi-monthly cleanup may suffice.

Monitor Database Size

It’s important to keep an eye on your database size to determine how frequently you need to schedule cleanups. You can use plugins like WP-Optimize, WP-Sweep, or Advanced Database Cleaner to monitor your database size and set up automatic cleanups. Alternatively, you can monitor your database size using cPanel.

Frequently Asked Questions

Why is my WP database so big?

Your WordPress database may be big because of accumulated data over time. This includes post revisions, spam comments, and unused data like media files, themes, and plugins. Additionally, some plugins may create their own tables in the database, which can also contribute to its size.

Does WordPress store everything in the database?

No, not everything is stored in the database. WordPress stores content like posts, pages, and comments in the database, but media files like images and videos are stored on your server’s file system. Plugins and themes may also store data outside of the database.

What happens if I accidentally delete important data from my database?

If you accidentally delete important data from your database, it may result in errors or a broken website. This is why it’s vital to always back up your database before performing any cleanup. You can then use the backup to restore any accidentally deleted data.

What happens if I delete my WordPress database?

Deleting your WordPress database will result in a broken website. Without the database, WordPress won’t be able to access any content, comments, or settings, and your website won’t function properly.

Take it away

А clean WordPress database is essential for optimal website speed, performance, and security.

There are several cleanup techniques that you can use to keep your database clean, including backing up your database, manually optimizing your database via phpMyAdmin by going over database tables and removing unused data like themes, plugins, media files, posts, and tags.

There are also several plugins available that can help you with WordPress database cleanup, including WP-Optimize, WP-Sweep, and Advanced Database Cleaner. When choosing a plugin, it’s important to consider its features, ease of use, and reliability.

By scheduling regular cleanups and monitoring your database size, you can ensure that your website remains fast, secure, and optimized for optimal user experience. Prioritize a database cleanup today and reap the benefits tomorrow.

Source :
https://nitropack.io/blog/post/wordpress-database-cleanup-guide