Skip to content

SQL queries to clean up a WordPress database

WordPress is a very popular free tool for building a website or blog. It is well designed, but sometimes there are some unhelpful features that can make the database cluttered. To gain performance and lighten this database, it is possible to perform some handy maintenance SQL queries to purge information that is of little use or even no longer used at all. This article presents a list of SQL queries that will surely be useful to people who own a site under WordPress.

WordPress logo

Prerequisite

Please note, these requests have been tested for at least version 3.5 of WordPress, but nothing says that they will still be operational in the future, if the structure of WordPress changes. In all cases, It is strongly recommended to make a backup of the WordPress tables before using one of the queries presented here.

Practical information : by default all WordPress tables are prefixed with “wp_”. However, during installation it is possible to modify this prefix, in particular for security reasons. If you have modified the prefixes, you will have to remember to adapt the requests presented in this article.

Lighten the table of articles by removing revisions

By default WordPress saves a post that is being edited every X seconds. In addition, each time the author saves an article, the tool adds a record in the database. We can then imagine that for 10 articles visible on a blog, there may be 50 articles registered in the database. If you don’t need all of this and want to purge the list of revisions, you can perform the following query:

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)
WHERE a.post_type="revision";

Purge spam comments

Comment spam is common on a WordPress blog. Any comments that have been classified as spam can be deleted from time to time to lighten the database. For this, you can use this query:

DELETE FROM `wp_comments`
WHERE `comment_approved` = 'spam';

To delete all comments awaiting moderation, all you have to do is use the same request, replacing ‘spam’ with ‘0’. This can be useful during a very large comment spam attack. And to remove all posted comments, replace ‘spam’ with ‘1’.

Purge deleted comments meta data

Comments may be deleted, for example with the request that deletes spam. However, the “wp_commentmeta” table may unnecessarily keep data linked to comments that no longer exist. To ensure that you only keep meta data from existing comments, you should perform this query:

DELETE FROM `wp_commentmeta`
WHERE `comment_id` NOT IN (
                        SELECT `comment_id`
                        FROM `wp_comments` )

Delete Akismet related entries

Very greedy in disk space, the Akismet plugin generates several meta data by comments. By deleting this data and particularly those related to spam emails that have been deleted, it is possible to save several MB of unnecessary data.

DELETE FROM `wp_commentmeta`
WHERE `meta_key` LIKE '%akismet%'

Purge meta data from articles that no longer exist

If the articles have been deleted, there may be linked meta data that is no longer useful at all. To delete them and potentially gain several MB on a database, you can use this query:

DELETE pm
FROM `wp_postmeta` pm
LEFT JOIN `wp_posts` wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

Delete all pingbacks

If a blog no longer wishes to display pingbacks in comments, then they can all be removed in 1 single request:

DELETE FROM `wp_comments`
WHERE `comment_type` = 'pingback';

Remove a shortcode in the content of an article

Shortcodes are very useful for adding specific functionality inside an article. However, it is tedious to have to edit all the articles manually to delete a just deleted one. To delete one, simply perform the following request, adapting it with the name of the shortcode to delete:

UPDATE `wp_post`
SET `post_content` = REPLACE(`post_content`, '[vieux_shortcode]', '' );

Delete a custom field

It is possible to delete a custom field on all the articles where it is used, in a single request. To delete a meta, you must use this query, to adapt with the name of the custom field to delete:

DELETE FROM `wp_postmeta`
WHERE `meta_key` = 'meta_a_supprimer';

Delete unused keywords

Keywords, also called “tag”, can be quite numerous on a blog. Over time, if articles are deleted or if they de-reference certain keywords, there may be keywords that are not associated with any article. To purge these keywords which are no longer used by the articles, you should use the following 3 queries:

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 );

Purge the feed cache

By default WordPress stores a cache of feeds (RSS, ATOM, etc.). To purge this cache and thus free a large number of records from the wp_options table, you can perform this query:

DELETE FROM wp_options
WHERE option_name LIKE ('_transient%_feed_%')

Delete old articles

For some type of event blog, it is advisable to remove outdated content. Even if it is not recommended in terms of SEO on Google, it is possible to delete all the old articles, more than 365 days old (to be adapted) thanks to this request:

DELETE FROM `wp_posts`
WHERE `post_type` = 'post'
AND DATEDIFF(NOW(), `post_date`) > 365

It is also possible to delete articles from a date, rather than from a number of days. To do this, you must prioritize this query:

DELETE FROM `wp_posts`
WHERE `post_type` = 'post'
AND `post_date` < '2010-12-31 23:59:59'

Remove “user agent” information from each comment

By default WordPress records the user agent of each visitor who leaves a comment. This provides useful information on visitors, in particular to know the most used browsers. On a computer support blog, it can even help the blogger to understand the visitor's computer configuration, for the sake of computer support. However, in the majority of other cases, this information is superfluous and takes up space in the database. To delete user agent information for comments already recorded, you should perform this request:

UPDATE `wp_comments`
SET `comment_agent` = '';

More query?

If you know of any other queries that lighten the database and purge unhelpful data, you can share them in the comments of this article.