After using wordpress for some time, you will notice that the wp_postmeta table is getting bigger.
You can reduce ‘overhead’ by optimizing and clean up the wp_postmeta table by:
Log in to PhpMyAdmin.
Click on the database.
Click on wp_postmeta.
Click on SQL and run the following query:
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
Yes, you can run the DELETE. Personally, I would create a backup table of the rows that are going to be deleted, and then reference that.
The SELECT uery demonstrates the anti-join pattern
SELECT pm.*
FROM wp_postmeta pm
-- anti-join - only return rows from pm that do NOT have a matching row in wp
LEFT
JOIN wp_posts wp
ON wp.id = pm.post_id
WHERE wp.id IS NULLThe LEFT JOIN is an outer join; that says return all rows from the table on the left (in this case pm ) along with matching rows from the table on the right ( wp ).
One way to think about what the outer join does is that it says if there are no matching rows in wp, then go ahead and create a dummy row that consists of all NULL values, and return the dummy row.
The trick in this query is the condition in the WHERE clause. Any row from wp that matches is guaranteed to have a non-NULL value for wp.id, because only non-NULL values will satisfy the equality comparison in the ON clause. Any NULL value won’t be matched. (Also, its likely id is a column guaranteed to be non-NULL in the table.)
After the join operation, any resulting rows that have a NULL value for wp.id are the result of dummy rows generated by the outer join. So we know there was no matching row in wp.
Another way to generate an equivalent result is to use a NOT EXISTS (correlated subquery) predicate.
SELECT pm.*
FROM wp_postmeta pm
WHERE NOT EXISTS
( SELECT 1
FROM wp_posts wp
WHERE wp.id = pm.post_id
)I’d create a backup of the rows to be deleted:
CREATE TABLE _backup_db_._backup_20190813_delete_from_pm_
AS
SELECT pm.*
FROM wp_postmeta pm
-- anti-join - only return rows from pm that do NOT have a matching row in wp
LEFT
JOIN wp_posts wp
ON wp.id = pm.post_id
WHERE wp.id IS NULLAnd then I’d reference the backup table in the DELETE, with join that references the primary key of the target table.
Assuming here that id is the PRIMARY KEY in the post_meta table (we would need to check that, not just guess), I’d write first as a SELECT:
SELECT t.*
FROM _backup_db_._backup_20190813_delete_from_pm s
JOIN post_meta t
ON t.id = s.id to test, and then convert to a delete statement by replacing the SELECT keyword with DELETE keyword
Note that it’s critically important that the * be qualified with the table alias t.*.
Your WordPress database is like a filing cabinet for your website that stores all of your content, including posts, pages, comments, revisions and spam comments, as well as the settings for your themes and plugins. So if you’ve been using WordPress for a while, chances are your database is cluttered and filled with tables you no longer need.
This useless data leads to database bloat (I mean, do you really need to save the settings for themes you deleted years ago?), so cleaning up your database not only helps speed up your site so it loads faster, but can also clear up significant space in your database so it runs more efficiently.
With WordPress, there are a few different ways you can tackle optimizing your database. In this post, we’ll look at some mySQL queries you can use to clean up your database in phpMyAdmin as well as some great plugins that make the task even easier.
Note: Before making changes to your database, I highly recommend you backup your website first. Whether you’re making small changes to your site or big ones, having a backup of your site ready to restore will give you peace of mind if something goes wrong. A plugin like BlogVault makes backing up your site easy.
Optimizing Your WordPress Database with phpMyAdmin
There are several ways you can run SQL queries on your database, but if you have cPanel on your server the best and easiest option is phpMyAdmin.
To access phpMyAdmin, login to cPanel for your site and click “phpMyAdmin” in the “Databases” section.
Once you’re in phpMyAdmin, you’ll see your website’s databases listed on the left. Click on the one you want to clean up and then click the “SQL” tab.
In the image above, I’ve blurred the name of my database, but you get the idea – I’ve selected the first database for my site. (I have a few databases to optimize since I used to have Multisite installed on my server!)
The SQL section in phpMyAdmin is where you can enter SQL commands and then hit “Go” to run them.
It’s important to note that this article uses the default table prefix wp_, so make sure you change the prefixes in the SQL commands below match the ones used by your database.
Delete Old Plugin and Post Data
Let’s start with deleting leftover data from plugins you no longer have installed. The wp_postmeta table also happens to be where your post data is stored, so when you run this query you’re hitting two birds with one stone.
[code class=”php”]DELETE FROM wp_postmeta WHERE meta_key = ‘META-KEY-NAME’;[/code]
Don’t forget to replace META-KEY-NAME with the value you want to clear out.
Delete Post Revisions
Old post revisions quickly add up, especially if you have authors on your site who are constantly saving their work over many days. If you want to delete all of the post revisions in your database in on hit, run this query:
[code class=”php”]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’;[/code]
Thanks to Joseph Michael Ambrosio for this query, which removes all revisions without unintended data loss and accidentally deleting link relationships.
Delete Spam Comments
It’s a chore deleting spam comments in batches, let alone one-by-one, but you can remove them all in one go with this query:
[code class=”php”]DELETE FROM wp_comments WHERE comment_approved = ‘spam’;
[/code]
Delete Unapproved Comments
Here’s a query for admins who are too lazy to check comments! Simply run this this query to bulk delete all unapproved comments.
[code class=”php”]DELETE from wp_comments WHERE comment_approved = ‘0’;
[/code]
Delete Unused Tags
It’s easy for tags to accumulate over time, especially if you add them to posts and then change your mind and delete them. Also, tags have fallen out of favour in recent years as many bloggers have stopped using them. This query will delete all tags that aren’t associated with any posts.
[code class=”php”]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);
[/code]
Delete Old Shortcodes
Like old plugin data, forgotten shortcodes often hang around in your database long after you’ve stopped using them, or deleted plugins they were associated with. Instead of editing posts and pages manually to remove shortcodes, run this query to remove all instances of a shortcodes on your site.
[code class=”php”]UPDATE wp_post SET post_content = replace(post_content, ‘[YOUR-SHORTCODE]’, ” ) ;[/code]
Don’t forget to replace [YOUR-SHORTCODE] with the unused shortcode you wish to remove.
Delete Pingbacks and Trackbacks
Does anyone use pingbacks or trackbacks anymore? Use these two queries to remove data for both pingbacks and trackbacks from your site.
[code class=”php”]DELETE FROM wp_comments WHERE comment_type = ‘pingback’;
DELETE FROM wp_comments WHERE comment_type = ‘trackback’;[/code]
Make sure you’ve disabled pingbacks and trackbacks before running these queries.
Delete Transients
Transients provide a way to temporarily store cached data in the database by giving it a name and a timeframe after which it will expire (hence the name “transient”) and be deleted. Sometimes, transients set by WordPress and plugins can take up a lot of space in your database, by they can be safely removed using this query:
[code class=”php”]DELETE FROM wp_options WHERE option_name LIKE (‘%\_transient\_%’)
[/code]
Optimizing Tables
While you’re already logged into phpMyAdmin, why not optimize your tables? In a few clicks, you can quickly optimize your tables yourself without having to install any plugins.
In phpMyAdmin, on the “Structure” tab and then click on the database you want to optimize. At the bottom of the list click “Check all”. In the dropbox box beside this option, select “Optimize table”.
phpMyAdmin will automatically start optimizing your table as soon as your select the option in the dropbox and will then display the message “Your SQL query has been executed successfully.”
Optimizing Your WordPress Database with Plugins
There are a few free and premium plugins available that can help clean up your WordPress database with minimal effort. After testing lots of different options, WP-Optimize and WP-Sweep, in my opinion, are the best in terms of ease of use and actually optimizing your database well.
WP-Optimize
With more than 600,000 active installs, WP-Optimize is the most popular database optimization plugin for WordPress. It’s super easy to use, simply click “Run optimization” next to the clean up options you want to run.
The “Table information” tab displays all of the tables in your database along with their size. The screen also tells you the total size of your database and how much total space the plugin can help you save.
Under the “Settings” tab, you can choose to schedule database optimization so it runs automatically every week, fortnight or month, and even log all changes for you.
Overall, it’s a really simple plugin to use. Just remember to backup your site before using it.
Discover WP-Optimize
WP-Sweep
WP-Sweep is quickly gaining a following (currently at 50,000 active installs) because it was developed by Lester Chan, a popular WordPress developer.
When you install the plugin, go to “Tools > Sweep” to access the settings. The plugin has an intuitive interface that displays a report of how much unnecessary data is in your database. It’s divided into different sections for posts, comments, users, terms, options and optimizing tables.
When you click “Sweep” beside an entry, the plugin gets to work optimizing your database for that entry. If you want to sweep your whole database, just click “Sweep All” at the bottom of the page.
Unlike WP-Optimize, WP-Sweet uses WordPress delete functions as much as possible to clean up your database instead of running direct delete MySQL queries, ensuring orphaned data isn’t left behind in your database. However, WP-Sweep doesn’t offer automated database optimization.
Discover WP-Sweep
WP Rocket
Did you know WP Rocket also includes database optimization? Just click on “Database” in the WP Rocket settings to access this easy-to-use feature.
The interface is super intuitive. At a glance you can see exactly how many tables are available to optimize. There are five different sections, including posts, comments, transients, and database cleanup, which allow you to clean up revisions, trashed posts, transients and more. There’s also a section for scheduling automatic database cleanups, which you can set to run daily, weekly or monthly.
In order to clean up your database, simply check the options you want to sweep and click “save and optimize”. WP Rocket will then get to working optimizing your database.
What I like about WP Rocket’s database optimization features is how quick and easy it is to use without the need to install another separate plugin. Plus, with automatic cleanup enabled, there’s no need keep checking back – WP Rocket keeps my database tidy for me.
Discover WP Rocket
Conclusion
I hope this article helps you optimize and speed up the performance of your database and WordPress site. While phpMyAdmin can be a bit overwhelming to use at first, running SQL queries is fairly straightforward, though plugins make it even easier to clean up your database. Just remember to always backup your site before making any changes to your database.
Due to this I believe it should be OK to purge it. However, remember to backup your database prior to removing these entries.
Once done with the database backup log in to phpMyAdmin, select the right database & click on “SQL” menu on the top.
There are two (2) steps to fully remove the junk.
1.) Clean up “wp_commentmeta” entries which have no relation to wp_comments
Use the following command to look at “junk” entries. It also shows the number of “junk” entries.
|
1
2
3
4
5
|
SELECT * FROM wp_commentmeta WHERE comment_idNOT IN (SELECT comment_idFROM wp_comments) |
Once satisfied, execute the following command to remove the “junk” entries.
|
1
2
3
4
5
|
DELETE FROM wp_commentmeta WHERE comment_idNOT IN (SELECT comment_idFROM wp_comments) |
After optimizing the database, the table size drop to ~5MB.
2.) Clean up Akismet related metadata in wp_commentmeta table.
Use the following command to look at “junk” entries. It also shows number of “junk” entries.
|
1
2
|
SELECT * FROM wp_commentmeta WHERE meta_keyLIKE "%akismet%" |
Once satisfied, execute the following command to remove the “junk” entries.
|
1
2
|
DELETE FROM wp_commentmeta WHERE meta_keyLIKE "%akismet%" |
After perform database optimization, table size drop to 0MB. That is a whopping 8MB of junk.
\
You should now have a new table with the name wp_commentmeta_bk. Repeat the same setups for wp_postmeta table. You may enter “wp_postmeta_bk” as the name for backup or choose your own name. Alternatively, you could export the whole database as a file and save it on your computer.
Clean wp_commentmeta and wp_postmeta tables
Cleaning wp_commentmeta
First let us clean wp_commentmeta table. As shown in the picture below, go click on your database name on the left side and then go to the SQL tab.
SQL Query for wp_commentmeta clean up
In the text box and as shown above, enter the SQL query give below. Ensure that a semi-colon is selected as the delimiter and the press “Go”.
SELECT * FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments ); DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments ); SELECT * FROM wp_commentmeta WHERE meta_key LIKE '%akismet%'; DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';
There are 4 separate SQL queries that are executed in sequence. First, we select the commentmeta data for comments that do not exist (spams or deleted comments) and then we delete them. Next we select commentmeta data that are Akismet validations and then we delete them. On successful execution, you should see “Your SQL query has been executed successfully”. You should also see how many rows were affected.
Cleaning wp_postmeta table
To clean wp_postmeta table, again click on the database name on the left side and then go the SQL tab. Enter the following SQL query into the textbox, ensure that semi-colon appears as the delimiter and hit “Go”.
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL; DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
Here too, we first select the postmeta data for posts that do not exist anymore and then we delete them. After execution, you should see the successful execution message and the number of rows that were affected.
Optimize wp_commentmeta and wp_postmeta Tables
We have cleaned up wp_commentmeta and wp_post meta tables but not really. The rows that were clean will reside in the table as “overhead”. You will have to optimize the table to get rid of them. Fortunately, phpMyAdmin makes it very easy to perform MySQL tasks. [Read: 10 easy phpMyAdmin tweaks to simplify MySQL administration].
To optimize wp_commentmeta table, click on the table name on the left size and then go to the “Operations” tab. Under “Table maintenance” click on “Optimize table”. Repeat the steps to optimize wp_postmeta table. Alternatively, you can check all tables and from the “With selected” drop down menu select “Optimize table”. Now go back to the database structure view on phpMyAdmin and compare the number of rows and size of wp_commentmeta and wp_postmeta table.
Notice that wp_commentmeta table reduced from 6.0 MiB to 55.8 KiB (5,387 rows to 592 rows). That is a big reduction / saving. The wp_postmeta also showed size reduction. After thoroughly testing and making sure that you did not break your database or lose any data, you may delete the backups of wp_commentmeta and wp_postmeta tables you made.
There are several posts on WordPress forum asking how to clean wp_commentmeta and wp_postmeta and hopefully this post helps others who are having troubles.. So there you go, optimize wp_commentmeta and wp_postmeta table and clean up database to improve your WordPress performance.