The WordPress database is the filing cabinet for your website. Which stores everything from content to posts, pages, comments, revisions, and even settings for the themes and plugins of your site.
So, if you’ve been using WordPress for a long time, there are chances that your database is likely to be cluttered with things you no longer need. This useless database takes up a lot of space and even slows down your site load. Which ultimately impacts your site’s user experience.
In that case, doing regular cleanups of your WordPress site can help you to reduce the database size. Therefore, I’ve come with this guide to share how to clean WordPress database manually. So, let’s take a look at them.
Things You Need to Clean
Everything that you do on WordPress is saved in the database, and as your site grows, it accumulates more databases, which makes it bloated. By cleaning and optimizing this database, you can save valuable seconds off your site loading time. However, a clean WordPress site is good for search engine crawlers.
So, here is a quick list of things that you need to clean and optimize your site.
- Spam comments.
- Trashed comments.
- Auto drafts or autosave of posts.
- Pingbacks and trackbacks.
- Database entries left in the database by plugins you have removed.
- Database entries left by the themes you have removed.
- Expired transients.
- Post revisions
- Auto drafts
- Unapproved comments
- Unused plugins
- Orphaned post meta, term relationships, comment meta, user meta
- Duplicate post meta, user meta, comment meta
- Deleted posts
- embed caches
Always Backup Your Database
Like I said before, your database contains everything needed to run your WordPress site. This implies, one wrong move or deleting a wrong thing can break your site entirely. That’s why it’s always recommended to backup your website before making any changes to the databases.
It doesn’t matter whether you’re making small or big changes; having a backup of your site will ensure peace of mind if something goes wrong. Here, you can use plugins to do the backup process.
Or, if you don’t want to use any plugin, then use FTP programs like FileZilla to download files and use phpMySQL to download the database. Another way is, connecting the site to a cloud storage system like Dropbox and do the backup. In this way, you can access the files from anywhere you have an internet connection.
How to Clean WordPress Database Manually
Though several plugins can help you clean all your unnecessary databases faster, it is still possible to clean them manually without using any plugin.
Well, WordPress software uses PHP and MySQL.PHP programming languages to access and display the stored databases. The phpMyAdmin and MySQL database includes all the information about your WordPress website. So, here’s how to clean WordPress database manually.
- Log in to your cPanel, and on the dashboard, navigate to DATABASES.
- Here you’ll find a tool called phpMyAdmin.
- Click on this tool, and you’ll see your website’s databases listed on the left.
- The table column shows the name of each database of your site.
- Make sure not to hurry, as you can delete the wrong one. Go table by table to identify the specific data you want to delete.
- After that, select the database that you want to delete and click on the SQL tab to enter the SQL command. Then hit GO to run them.
- Click MySQL Databases under Databases in cPanel.
- Then under Current Databases, locate the database you want to delete, and click Delete.
- After that, a new screen will appear and prompt you to confirm the deletion of the selected database. Make sure you are deleting the correct database and click Delete Database.
SQL Queries to Clean Up Your WordPress Database
Remember, Any of these queries should be preceded by a backup of your whole database. And make sure to replace the wp- table prefix with the prefix used on your WordPress website. Otherwise, the queries won’t work.
Delete Post Revisions and Their Metadata
Revisions are useful features, but if you keep them for longer and don’t delete them from time to time, your database will quickly become big.
DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);
Delete Old Plugin and Post Data
With this single query, you can clean two databases. Make sure, replacing the “META-KEY-NAME” with the value you want to clear out.
DELETE FROM wp_postmeta WHERE meta_key = 'META-KEY-NAME';
Delete Spam Comments
It’s tiring to delete spam comments one by one. But with this query, you can remove them all.
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Delete Unapproved Comments
This query is for bulk delete of all unapproved comments.
DELETE from wp_comments WHERE comment_approved = '0';
Delete Old Shortcodes
Unused old shortcodes hang around your database and create readability issues. Therefore use this query to clean them.
And make sure to replace [YOUR-SHORTCODE] with your unused shortcode.
UPDATE wp_post SET post_content = replace(post_content, '[YOUR-SHORTCODE]', '' ) ;
Delete Unused Tags
When you add tags to posts and then delete them, they tend to accumulate over time. In this case, the following query will delete all the tags that aren’t associated with any of your site posts.
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 ); DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms); DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
Delete Pingbacks and Trackbacks
If you are using pingbacks and trackbacks, use the following query to remove them. Also, don’t forget to disable both these before running the queries.
DELETE FROM wp_comments WHERE comment_type = 'pingback'; DELETE FROM wp_comments WHERE comment_type = 'trackback';
Sometimes WordPress and plugins set unnecessary transients that take up a lot of space in your database. Here, you can use this quiet to clear the transients.
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');
Delete Feed Cache
WordPress stores the feed cache in the wp_options table. So, using the below query you can clear the feed cache.
DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient%_feed_%')
Remove Comment Agent
When someone leaves a reply on your blog, WordPress saves the user agents in the database by default. So, to delete these comments, use this query-
update wp_comments set comment_agent ='' ;
Batch Delete Old Posts
There may be some articles that are no longer needed. In that case, you can delete them. The following query will help you to delete content older than 600 days.
DELETE FROM `wp_posts` WHERE `post_type` = 'post' AND DATEDIFF(NOW(), `post_date`) > 600
Use Plugins to Clean Your WordPress Database
You’ll find a few free and premium plugins to clean up your WordPress database with minimal effort. In terms of ease of use and efficiency, I’ll recommend you to use-
Simply install and activate this plugin from your WordPress dashboard. Once you’re done with the activation, go to WP-Optimize and select “run optimization” next to the clean-up options that you want to clean.
From the Table Tab, you’ll get all the tables in your database, along with their size and total space the plugin can save. The “Setting Tab” will show the schedule database optimization, so that it can run every week or month automatically.
Just like before, you have to install and activate this plugin, and once done, go to Tools > Sweep, and the plugin’s setting option will appear. Then, you just need to click “Sweep” beside each entry that you want to clean. If you want to clean all databases, simply select “Sweep All”
When it comes to improving your site performance, I’ll recommend making database cleanup a habit from now on. It may seem scary for the first time, but you can always make things easier with the right tools and processes.
And with this guide about how to clean WordPress database manually, you can definitely do it easily. But, if you are not a tech-savvy person and still feel confused, then you can hire a professional.
That’s all for today. Go ahead with the right clean-up process. And let me know how this guide helps you in the comment section below. Also, you can Contact Us for any sort of queries.