27 Handy SQL Query Hacks for WordPress

If you have been managing a WordPress website for a while then you probably know that this content management platform uses a MySQL database to store every bit of information it needs to display your pages and apply your chosen settings.

WordPress and MySQL make a great partnership; both are free, and using WordPress means a constant source of new plugins and features to make life easier. But you can manage your site just as well, and in some cases even better, from phpMyAdmin if only you know how to work from the MySQL side. That, of course, is just a matter of running queries against the database WordPress relies on.

It’s not essential to know MySQL to build a WordPress site, but knowing how to run some useful queries that will save you headaches is definitely an advantage.

How to Manage your WordPress Database

If you’re familiar with phpMyAdmin and SQL (structured query language), skip ahead, but if this is new:

You can find phpMyAdmin on your hosting cPanel if you don’t know where to access it. When the console opens, select your site database from the list on the left. It should reflect the domain or username given to you when your WordPress site was set up, followed by suffix “_wp”, if the name hasn’t been changed. It is advised you don’t mess with the information_schema database at this point.

If you see multiple databases and can’t identify the proper database, select them one at a time, and each time open a new query window by clicking the SQL tab. Type in:

SELECT * from wp_options

Check the entries under the column named option_value. You should recognize your siteurl, blogname, and other information right there on the top few rows. If you the correct info is there, you have the right database. Before you do anything else, feel free to explore the tables and column names to see what you’ve got here. You have to select a database (and thus its tables) to run queries against it. If you want to see what’s in each table, use the SELECT * query above, swapping out wp_options for whatever table name you want to explore. Just don’t change any values yet.

First Step: Backup your Database

WordPress needs the information in the database to re-create every last post, comment, and setting you’ve created. No matter how competent you are with SQL, remember that one typo or mistaken click could destroy information that corrupts your database to the point where your site won’t load at all. Any kind of UPDATE or DELETE query could mean loss of critical site or content information. Backing up the database before you start tinkering with your data means you can always put it back just the way it was.

You can download plugins like WP-DB-Backup or WP-DBManager to assist you with this from your admin dashboard, but you can also do it straight from phpMyAdmin before you get started. The quickest and easiest way:

1. Login to phpMyAdmin.
2. Select your WordPress database.
3. Click on Export at the top of the window
4. In the FORMAT drop-down list, select SQL to export as a .sql file. It should be the default choice.
5. Click Go, and a copy of your database will be downloaded.

If you wanted to, you could click the Custom button for other options.

Alternately, you could go to the Operations tab, enter a file path in the Copy Database To box, select options, and click Go.

Restore a backup of your MySQL database

1. To restore the database, click the Import tab.
2. Leave the FORMAT as SQL or change it to whatever format was used
3. Browse to where you saved your backup of the database.
4. Click Go.

Note that phpMyAdmin does have some file size limits, so if your database has grown very large you’ll have to either trim it down using some of the queries below, or try another method such as one of the plugins. Upload size is usually 2MB, and these are the settings to look for:

post_max_size = 8M
upload_max_filesize = 2M

Note that post_max_size must be the same or larger than upload_max_filesize.
These settings may be set globally across the server depending on your installation, and you can’t change them. If not, you may find them in your php .ini file.

On Apache installations you may be able to change these settings locally by putting a “.htaccess” file in phpMyAdmin’s directory. The contents of the file should be:

php_value post_max_size 20M
php_value upload_max_filesize 20M

Change ’20M’ to however large you want file limits to be. If you get Internal Server Errors (500) then this likely means your host does not allow you to change these settings and you need to remove this file.

If you’re restricted by file sizes, you’ll just have to make multiple file backups by selecting the Custom checkbox and choosing individual tables for each backup file rather than the entire database, and restoring them one at a time.

SQL Hacks for WordPress Users

Now that that’s out of the way, here are some sample queries you can run from that same query box that will help in managing WordPress and MySQL databases:

1. Change your URL

WordPress stores the absolute path for your site URL and home URL. If you transfer your site to another server or domain, it will not load if the URLs are not updated. You can do this by running:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.myoldurl.com', 'http://www.mynewurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';

2. Update GUID (Globally Unique Identifier)

If you have uploaded your blog site from your computer to a new server or new domain, you also should update the URLs for the GUID field, since this is used to translate the post slug to the absolute path.

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.myoldurl.com', 'http://www.mynewurl.com');

3. Update URL in Content

Within the content of each post’s data, MySQL stores the old URLs referencing the old source. You need to change these to the new domain.

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.myoldurl.com', 'http://www.mynewurl.com');

4. Update Image Path

You might have heard about using Amazon CloudFront as a Content Delivery Network (CDN) to offload image delivery. After you’ve created a CNAME record, use the query below to update image paths in WordPress to load from Amazon CloudFront.

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.myoldurl.com', 'src="http://yourcdn.mynewurl.com');

You also need to update the GUID for image attachment:

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.myoldurl.com', 'http://yourcdn.mynewurl.com') WHERE post_type = 'attachment';

5. Add a New Field to Posts

This query adds a custom field to every post on your site. You can name the new field anything you like to easily identify it later by changing ‘MyNewCustomFieldValue’ to what you want (it must not already exist).

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
 SELECT ID AS post_id, 'UniversalCustomField'
 AS meta_key 'MyNewCustomFieldValue AS meta_value
 FROM wp_posts WHERE ID NOT IN
 (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
 '' AND post_type = 'post';

The following will do the same for all the pages on your site:

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
 SELECT ID AS post_id, 'UniversalCustomField'
 AS meta_key 'MyCustomFieldValue AS meta_value
 FROM wp_posts WHERE ID NOT IN
 (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
 AND 'post_type' = 'page';

6. Find all Posts with Field Name

If you need to find all the posts with the new field you’ve created (or any of the existing fields) you can use the following query, being sure to replace ‘FIELD_NAME’ with the name of the field you are searching for. Of course, you can also do the same for pages by changing ‘post_type’ = ‘post’ to ‘post_type’=’page’.

SELECT wp_posts.ID, wp_postmeta.meta_key
 FROM wp_posts
 JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
 AND wp_postmeta.meta_key = 'FIELD_NAME'
 WHERE wp_posts.post_type = 'post'
 order by wp_posts.ID asc

7. Delete Post Meta

Every time you install a plugin WordPress makes changes to the wp_postmeta table. After you remove the plugin, the data will still be there as useless clutter. You can clear it up with this query; just remember to change ‘UselessMetaKey’ to the appropriate value.

DELETE FROM wp_postmeta WHERE meta_key = 'UselessMetaKey';

8. Change Default Username

WordPress creates the default user account under the name “Admin”. This is so obvious that it can be a problem if a hacker or someone else intercepts your account password. They just login and take or delete what they want. For your own peace of mind, you can change this default username.

UPDATE wp_users SET user_login = 'Awesome Boss' WHERE user_login = 'Admin';

9. Identify Unused Tags

If you delete your old posts, the old tags for each will remain in the database. This query allows you to identify all of the old tags.

SELECT * 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;

10. Deleting Spam

If you run a blog, you’ve gotten sick of all those spam comments piling up. This one SQL command will clear out all the comments you’ve marked as spam:

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

11. Reset Password

Here’s a handy one if you’ve ever forgotten your WordPress password or just want to change it (or someone else’s):

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'myusername';

12. Re-assign Articles

If you want to take ownership of some old legacy articles that came with the site, or that you now own the rights to, you can easily do it, just so long as you know the ID of old and new authors (which you can get from the Author & User page in the admin panel). Just click the author’s name and look for the user_id field.

UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'previous-author-id';

13. Delete Revisions

If you edit a post multiple times in WordPress, it will save revision copies, which can add up to quite a bit over time and is a huge waste of resources slowing down your database. Clear them out like so:

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'

This will delete all revisions; if you only want to delete specific ones you’ll have to retrieve the ID number and add it to the WHERE clause.

14. Delete All Unapproved Comments

Swamped with new comments you don’t have time to look at? Try this:

DELETE FROM wp_comments WHERE comment_approved = 0

15. Disable Comments on Old Posts

For this query, you can specify the comment_status as ‘open’, ‘closed’, or ‘registered_only’, as well as including date criteria.

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2016-03-11' AND post_status = 'publish';

16. Export Comment Emails as Unique Records

Accumulating hundreds of comments can also lead to dozens of comments from the same email address. If you want to export all those emails for mailing campaigns or other needs, you can create a list of distinct emails with this query:

SELECT DISTINCT comment_author_email FROM wp_comments;

When it has returned the resulting list, under Query results operations, select the Export option.

17. Delete Pingback

Your most popular posts could generate tons of pingbacks accumulating in the database. To get rid of them, use this query:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

18. Delete Comments from a Specific URL

If you’ve been getting a lot of spam from the same URL, you can remove all the garbage in one pass with the following query. The ‘%’ means you can delete all entries containing that portion of the URL string, which is handy to avoid typing URLs, but you may want to be careful not to be too general and delete URLs you didn’t mean to.

If you have spam comments that all contain the same URL, then this query allows you to remove them in one go. The following query will delete all comments with a particular URL. The ‘%’ means that any URL containing the string within the ‘%’ signs will be deleted, so I was sure to add the qualifier that this only applies to spam comments.

DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" AND wp_comments.comment_approved = 'spam';

19. Identify & Delete Posts that are over ‘X’ Days Old

If you ever need to identify and delete posts that are over a certain amount of days old, then this snippet will help.

To identify any posts that are over ‘X’ amount of days run this query, remembering to replace the ‘X’ with the number of days you are looking for:

SELECT * FROM 'wp_posts'
 WHERE 'post_type' = 'post'
 AND DATEDIFF(NOW(), 'post_date') > X

To delete any posts that are over ‘X’ amount of days run this query:

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

20. Change WordPress Posts into Pages (or Pages into Posts)

Changing posts to pages is easy enough:

UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'

… and if you want to change them back again:

UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page'

To change individual posts (pages) you’ll have to include the correct ID field in a WHERE clause.

21. Removing Shortcodes

One of WordPress’ features is that it allows us to reuse repeated snippets of code by identifying them via shortcodes. Shortcodes are convenient, but if you decide you don’t want to use one anymore, run this query, replacing “trashshortcode” with the name of the one you’re trying to lose.

UPDATE wp_post SET post_content = replace(post_content, '[trashshortcode]', '' ) ;

22. Find and Replace Post Content

If you find you need to update or change existing text across multiple posts, for whatever reason, the easiest way to do it is with the following easy query:

UPDATE wp_posts SET 'post_content'
 = REPLACE ('post_content',
 'OldText',
 'NewText');

23. Find Posts where Field is Missing

If you want to find all the posts or pages where, in fact, your new field has not been added, you can just use this query, again, being sure to change ‘FIELD_NAME’ to the name of the field you’re checking for, or changing ‘post’ to ‘page’ to find the pages where this field isn’t used.

Use this MySQL query to get all posts with a missing custom field. We’ll query post_type=page for the purpose of this tutorial. You will have to replace CUSTOM_FIELD_NAME with the actual custom field name.

SELECT wp_posts.ID, wp_postmeta.meta_key
 FROM wp_posts
 LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
 AND wp_postmeta.meta_key = 'CUSTOM_FIELD_NAME'
 WHERE wp_postmeta.meta_key is null and wp_posts.post_type = 'post'
 order by wp_posts.ID asc

24. Queries to Find Edited Posts

It can be sometimes handy to query recently modified (last month or last few days) posts in WordPress MySQL tables. Here are some handy queries:

If you’re constantly updating facts and figures in your posts, it can be easy to lose track. The following series of queries will help you to find posts that have been recently modified for specific time periods:

In the last month:

SELECT * from wp_posts where post_modified > NOW() - INTERVAL X MONTH
 ORDER BY post_modified DESC;

In the last week:

SELECT * from wp_posts where post_modified > NOW() - INTERVAL 1 WEEK
 ORDER BY post_modified DESC;

You can change the interval to whatever value you need, such as DAY or HOUR.

25. Find All Posts by Category

First, you will need to find the id for a category name. An easy way to find the id for a specific category of posts is the following query (replacing ‘GENERAL’ with the name of the category you had in mind:

select wp_term_taxonomy.term_taxonomy_id
 from wp_term_taxonomy join wp_terms on (wp_term_taxonomy.term_id = wp_terms.term_id)
 where wp_term_taxonomy.taxonomy = 'category' and wp_terms.name = 'GENERAL';

Then you can find all the posts by that term_taxonomy_id (replacing 999 with the correct ID):

select * from wp_posts join wp_term_relationships on (wp_posts.ID = wp_term_relationships.object_id)
 where wp_term_relationships.term_taxonomy_id = 999;

26. Which Posts Get the Worst Spam

You can find out the top 10 posts on your site that seem to attract the most spammers. You can always change the ’10’ to a 5 or 20, or whatever you’re interested in.

SELECT `comment_post_ID`, COUNT(*) as amount
 FROM `wp_comments` WHERE `comment_approved` = 'spam'
 GROUP BY `comment_post_ID`
 ORDER BY amount DESC LIMIT 0, 10

27. Monthly Posts

If you are allowing posts from guests or other authorized users, you can determine how many articles come from which author for a timespan of say, 1 month in this example.

When you’ve got a lot of articles to juggle, this can help generate data for post tracking, such as overall trends or site growth. If you wanted to, you could be more specific by adding post_author to the WHERE clause or specifying a particular MONTHNAME and YEAR.

SELECT wp_users.display_name AS writer, CONCAT_WS( ', ', MONTHNAME( `post_date` ) , YEAR( `post_date` ) ) AS `post_month` , COUNT( * ) AS `post_count`
 FROM `wp_posts`
 JOIN wp_users ON ( wp_posts.post_author = wp_users.id )
 WHERE `post_type` = 'post'
 AND `post_status` = 'publish'
 GROUP BY `post_month`
 ORDER BY `post_date`

As you can see, there’s almost nothing you can’t do with your WordPress site in the MySQL database.

There are a variety of good tutorials available out there, but hopefully, we’ve simplified a lot of tasks for you by providing the above examples.

Good luck!