Instant WordPress Database Optimization and Cleanup with Queries
Your WordPress database is like the trunk of the car where all your belongings gets stored. But, instead of groceries, camping gear or your gym bag, your database holds onto posts, revisions, drafts, comments (even spam-my ones!) as well as theme settings and plugins.
Similar to a car, the more you keep adding, the worse the gas mileage gets. Or in the case of WordPress, the slower your website loads due to the extra bloat.
Periodically clearing out all the old data and optimizing your database helps keep your website running efficiently, reduces bloat to help your site load faster, and creates extra space for more content.
Today, I’ll share blazing fast cleaning and database optimization techniques through using MySQL queries and I’ll also share plugins that can also help make the process instantaneous.
Taking Care of Business
Before jumping right into database optimization and clean up, it’s important to back up your whole WordPress site or at least your database, especially if you’re not used to MySQL.
You can also note the size of the backup so you can compare your database after you have cleaned and optimized it.
For details, check out 7 Best WordPress Backup Plugins Compared (Pros and Cons) and 11 Free Quality Backup Plugins for Protecting WordPress.
How to Run MySQL Queries
One of the most popular ways of clearing out the WordPress database is by using MySQL, a data management system that WordPress uses to store and retrieve information on your site.
It uses Structured Query Language (SQL) and you can run commands called queries that manipulate data in the database to complete tasks such as removing unwanted stored data.
One of the best ways to run queries in MySQL is to do it from the popular MySQL database manager phpMyAdmin, which can be found in the control panel of your server such as in cPanel or in your local server environment if you’re working locally.
If you’re unsure of how to access phpMyAdmin, ask your website hosting provider as each one varies in its location.
After accessing phpMyAdmin and logging in, select the database that you would like to clean up and click the SQL tab. There should be an open box with the label Run SQL query/queries on database, followed by the name of the database you selected.
To run a query, enter one in the multi-line field and click go. If all went according to plan, and depending on the query, a report should appear on the screen as well as the notification: “Your SQL query has been executed successfully.”
Below are a series of common ways that WordPress databases can get clogged and overloaded along with the SQL queries to reverse the bloat.
Delete Old Plugin and Post Data
An effortless way to clean up your WordPress site is to go through all your plugins and deactivate and delete whatever you’re not using through the admin dashboard. You can also do this by deleting plugin file folders, but be sure you’re not using them.
Once you have deleted the files, all that extra lingering data is still not out of your hair. WordPress stores old plugin and post data in the wp_postmeta table so you can run the SQL query below to clean up anything you no longer need.
DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';
Remember to replace your-meta-key with the value that you want to clear out.
For details, check out How to clean up the WordPress wp_postmeta database table.
If you like to revise and rewrite posts on WordPress or if you have multiple authors doing the same thing, the extra data can really add up.
Run this query to delete all post revisions in the database:
DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id) LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id ) LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id) WHERE a.post_type = 'revision' AND d.taxonomy != 'link_category';
Delete Spam Comments
Spam is probably not something you intend to keep around, but actively deleting it every time it comes in is quite a time-consuming pain so more often than not it piles up.
This query should remove all your spam lickety-split:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Delete Unapproved Comments
If you have a bunch of post comments that you haven’t yet moderated or approved and you don’t plan on doing so, get rid of the whole bunch, and start anew with this quick query:
DELETE FROM wp_comments WHERE comment_approved = '0';
Delete Unused Tags
Tags can really accumulate over the course of setting up a site, especially if you keep changing your mind or want to overhaul systems. If any tags are unused in the system, the query below will rifle through the database and clear them out.
DELETE FROM wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = 'post_tag' AND wtt.count = 0;
Delete Old Shortcodes
Old shortcodes can hang around long after the plugins or themes they were connected to are deleted. It can also be a huge hassle to go through manually and take each one of them out.
Instead, use this SQL query to clear out everything that’s unusable quickly and efficiently:
UPDATE wp_post SET post_content = replace(post_content, '[your-shortcode]', '' ) ;
Delete Trackbacks and Pingbacks
Trackbacks and Pingbacks are both ways to reference and link to other articles that you have mentioned in your own blog posts. When other sites reference or link to your content, you get notified.
While they can be really useful, if your site starts to get popular or you end up being notified of spam postings, pingbacks and trackbacks can turn into a lot of bloat clogging up your database.
That’s why it has become typical for many website owners to turn off their trackbacks and pingbacks.
You can turn them off on your own site by going to Settings > Discussion in your WordPress admin dashboard, then uncheck the “Allow link notifications from other blogs (pingbacks and trackbacks) on new articles” box.
After you have disabled them, you can run the queries below to clear out any trackbacks and pingbacks that have piled up.
-- To Delete Pingbacks DELETE FROM wp_comments WHERE comment_type = 'pingback'; -- To Delete Trackbacks DELETE FROM wp_comments WHERE comment_type = 'trackback';
If you’re not sure if you have stacks of trackbacks or pingbacks that need to be thrown in the trash, it’s not a bad idea to run the above queries to make sure everything is cleared out.
For details, check out What are Trackbacks and Pingbacks in WordPress.
When you’re finished running any of these queries, database optimization is a couple clicks away and acts as a final reset before you get back on the road.
Optimizing your tables does three things to your database:
- Shrinks the data pages
- Shrinks the index pages
- Computes fresh index statistics
There’s a simple way to do this without the help of a plugin. In phpMyAdmin, select your database, then click on the Structures tab.
Select the Check All box toward the bottom of the page and select Optimize table in the drop down menu beside it.
Once that’s done, the database optimization tool will run automatically and results of the query should appear with the message “Your SQL query has been executed successfully.”
You can check out 18.104.22.168 OPTIMIZE TABLE Syntax for details.
There are a number of different plugins that can help with WordPress database optimization to make the process even more efficient without resorting to individual manual queries. Below are a couple standards to test out.
WP Optimize is one of the most popular database optimization plugins with over 700,000 active installations. It clears out comment data, pingbacks and trackbacks, old revisions and also has an automatic feature that lets you specify cleaning on a particular schedule.
WP Optimize will also let you know where there’s wasted space in your database so you can get rid of it and shrink it.
Advanced Database Cleaner is another popular plugin with over 30,000 active installations and a 4.8 rating.
This plugin lets you view unused items before doing a cleanup, and classify various database options and tables according to their association such as plugin options, theme options or WP core. You’re also able to delete all the unused and orphaned data that may have accumulated over your usage of the site.
Database Optimization Done and Dusted
Now that you’re armed with the knowledge of WordPress databases and their capacity for bloat, you can begin a regular maintenance regimen instead of waiting to notice your site sputtering along the road.
These mySQL queries and plugins will help you get your site back on track, freshened up and ready to rumble.
What does your database optimization and clean up schedule look like? Were you able to successfully scrub and optimize your database with the steps outlined above? Feel free to share your experience in the comments below.