How to Clean & Optimize WordPress Database?

Cleaning up and optimizing your WordPress database is essential for maintaining your site’s performance and ensuring its smooth operation. A cluttered database can slow down your site, increase loading times, and lead to other issues. If you’ve been using WordPress for a while, chances are your database is cluttered and filled with tables you no longer need. Now is the for a clean-up! But where to begin? Which content can be deleted without compromising the smooth running of your website?

Cluttered Database

To improve your site’s performance and reduce its overall load time, here’s a step-by-step guide on how to clean up and optimize your WordPress database:

First thing first: Before making any changes to your database, it’s highly recommended to back up your website and database.

1. Use a Plugin (Recommended): There are several plugins available that can help you clean up and optimize your WordPress database. Three popular choices are WP-Rocket, WP-Optimize and WP-Sweep. You can install and activate the plugin of your choice from the WordPress dashboard.

2. Database Cleanup using Plugins: Let’s briefly see how database optimization works for each of them!

  • WP-Rocket: plugin can be utilized to optimize your database in one click. It’s also the most powerful extension to boost your loading time, improve your PageSpeed performance score, and optimize your Core Web Vitals. From the WordPress dashboard, open the Database tab and select the advanced options of your choice. You can limit the number of revisions, trash the spammy comments and transients, and schedule an automatic daily, weekly, or monthly clean-up.wp-rocket
  • WP-Optimize: WP-Optimize cleans the database, compresses images, and caches pages. It clears out unnecessary data, cleans up your tables, and retrieves space lost to data fragmentation. There are also some stats about the database and the potential savings you could get after the optimization. Once the plugin is activated, go to the plugin settings. You will see options to clean up various database elements such as post revisions, spam comments, trashed posts, etc. Select the items you want to clean and click the “Run Optimization” button.wp-optimize
  • WP-Sweep: This plugin allows you to clean up unused, orphaned, and duplicated data, including post revisions, comments, and more. It provides a detailed breakdown of what will be cleaned, so you can review before proceeding. It cleans up revisions, auto drafts, unapproved comments, spam comments, and more. It also optimizes your database tables quickly and efficiently. After activating the plugin, go to “Tools” → “Sweep” in your WordPress dashboard.wp-sweep

3. Optimize Database Tables: There are several plugins available that can automate much of the database cleanup process. Some useful plugins like WP-Rocket, WP-Optimize and WP-Sweep can help you clean up your database tables, remove unused data, and optimize performance.

WordPress database tables can become fragmented over time. You can optimize them to improve performance. Optimizing tables can help reduce the database’s physical size, leading to improved performance. WordPress has following 11 tables by default, but more will be added according to the theme and plugin you install. Optimizing a database requires great care, and you must be cautious.

  • wp_users – Stores the users
  • wp_usermeta – Stores meta-information about users.
  • wp_comments – Stores all the comments.
  • wp_commentmeta – Stores meta information for comments.
  • wp_posts – Stores data for posts, pages, and other custom post types.
  • wp_postmeta – Stores meta information for posts.
  • wp_links – Creates, and stores links menu.
  • wp_options – Stores options set in the dashboard area.
  • wp_terms – Stores a list of tags and categories for all posts and links.
  • wp_term_relationships – Stores the relationship between a post and its category and tag.
  • wp_term_taxonomy – Taxonomy refers to a category, link or tag.

4. Delete Unused Plugins and Themes: Unused plugins and themes can leave behind unnecessary data in your database. Start by removing any unnecessary plugins and themes. These can clutter your database and slow down your site. Go to the “Plugins” and “Themes” sections in your WordPress admin dashboard and delete the one’s you’re not using.

To delete unused plugins, go to Plugins → Installed Plugins. Then, click Delete under any plugins you’d like to remove. Follow the same path from Appearance → Themes if you want to delete all the unutilized themes.

Similarly, you may check the Posts and Pages that have been sitting too long in your admin, and get rid of the posts and pages that will never be published.

5. Remove Spam and Trashed Comments: Over time, spam and trashed comments can accumulate in your database. To remove them, go to “Comments” in your WordPress dashboard, click on “Spam” and “Trash,” and then delete them permanently.

6. Clean Up Post Revisions: WordPress saves multiple revisions of your posts, which can bloat your database. You can limit the number of revisions saved or use a plugin like “WP Revisions Control” to manage them. Or to limit post revisions manually, simply add this code to your WordPress site’s wp-config.php file:

define( ‘WP_POST_REVISIONS’, yournumberhere)

Alternatively, you can run a database query to remove old revisions.

DELETE FROM wp_posts WHERE post_type=”revision”;

7. Remove Transient Data: Transient data is temporary information stored in your database by plugins and themes. While it’s meant to be temporary, sometimes it’s not properly cleaned up. Plugins like ‘WP-Rocket’, ‘Transient Cleaner’ can help you manage and delete transient data.

8. Manual Cleanup: If you’re comfortable with database management, you can manually review your database tables using phpMyAdmin or a similar tool. Look for tables that are particularly large and might contain unnecessary data. Always exercise caution when working directly with your database:

  • Login to phpMyAdmin: Access phpMyAdmin through your hosting control panel.
  • Select Database: Choose the database associated with your WordPress site.
  • Backup Database: Before making any changes, create a backup of your database.
  • Optimize Tables: In the database view, select all tables, and then from the “With selected” dropdown, choose “Optimize.”php-admin
    php admin               php admin
  • Repair Tables: If any tables are marked as “in use,” you can select them and choose “Repair” from the dropdown.
  • Delete Unnecessary Data: Carefully review the tables and delete any data you don’t need. Be cautious, as deleting the wrong data can harm your site’s functionality.

9. Regular Maintenance: Database cleanup should be performed regularly to keep your site running smoothly. Set a schedule for routine cleanups, and consider using a plugin to automate the process. Perform these cleanup and optimization tasks regularly to prevent your database from getting bloated again.

10. Monitor Performance: After cleaning and optimizing your database, monitor your site’s performance to ensure that the changes have had a positive impact. Check page load times, site responsiveness, and overall user experience. After performing these optimizations, monitor your site’s performance to ensure that it’s loading faster and functioning properly. Regular monitoring can help you catch any issues that might arise.

Summing up:

Remember that improper database manipulation can lead to data loss or site errors, so always proceed with caution, and make sure to have a backup before making any changes. If you’re not comfortable working with databases, consider seeking assistance from a developer or professional OR

you may take professional services at WordPromise with zero hassles and complete peace of mind!

Signup Today for premium WordPress support services.