WP can quickly become cluttered & outdated as time goes on. Post & page revisions, spam comments, outdated URLs & more can make your database & site troublesome to deal with — these queries will help clean things up.
WordPress is the most popular CMS around and for good reason. It allows anybody to build and maintain websites with ease. As time goes on, you may need to transfer a WordPress site to another URL, clean up your database to boost performance, or update some things that aren’t so easily done in the admin dashboard. That’s where this list of WordPress MySQL queries comes in handy.
Important: Before you proceed, backup your database before running any manual MySQL queries.
WordPress MySQL Queries Cheatsheet
If you don’t have terminal access, one of the easiest ways to run MySQL queries is through phpMyAdmin. Some hosts don’t provide this, but don’t fret — there’s a plugin that can help you out. The WordPress SQL Executioner gives you to ability execute MySQL queries through the admin dashboard.
- Update the site URL & home URL
- Update the GUID
- Update the URLs in your content
- Update image paths only
- Update your posts meta information
- Change the default “admin” username
- Change your WordPress password
- Assign posts from one author to another
- Delete revisions
- Delete post meta
- Export all comment emails with no duplicates
- Delete all pingbacks
- Delete all spam comments
- Delete unused tags
- Find how many entries are spam
- Find which posts suffer from the most spam
- Find where the most spam comes from
# Update the site URL & home URL
When you install WordPress for the first time, it stores the absolute path of the site URL and home URL in the database. This can make it a little tricky when you transfer your WordPress site from one URL to another. When you try, your site won’t load because the absolute URL is still set to the old URL. Fixing this is easy by updating the site URL and home URL in the database:
[code]UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldsiteurl.com’, ‘http://www.newsiteurl.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;[/code]
# Update the GUID
One of the most crucial updates to make after a site transfer is the GUIDs. This is important because the GUID is used to translate post and page slugs to the correct absolute path. Updating this is just as easy as updating the site URL and home URL:
[code]UPDATE wp_posts SET guid = REPLACE (guid, ‘http://www.oldsiteurl.com’, ‘http://www.newsiteurl.com’);[/code]
# Update the URLs in your content
Like the site URL and home URL, WordPress uses absolute paths when embedding media and links into your posts or page content. When you transfer a WordPress site to a new URL, you’ll need to make sure to update those URLs so they don’t get broke. This can be a tedious and time-consuming process if you have a lot of content to update. Running the SQL query below will make it fast and easy:
[code]UPDATE wp_posts SET post_content = REPLACE (post_content, ‘http://www.oldsiteurl.com’, ‘http://www.newsiteurl.com’);[/code]
# Update image paths only
If you use a CDN or cloud service like Amazon CloudFront to serve images, you’ll need to update the image paths in your existing posts and pages so they get loaded by the service instead of your server. Use the query below to only update image paths:
[code]UPDATE wp_posts SET post_content = REPLACE (post_content, ‘src="https://www.oldsiteurl.com"’, ‘src="https://yourcdn.newsiteurl.com"’);[/code]
# Update your posts meta information
WordPress stores special post information in a separate table. If you’ve transferred your WordPress site, you’ll need to update that information should any of it point to your old URL. Use the query below to easily do this:
[code]UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, ‘http://www.oldsiteurl.com’, ‘http://www.newsiteurl.com’);[/code]
# Change the default “admin” username
When you first install WordPress, it’s default account username is set to “admin”. Since this is widespread knowledge, it causes security concerns and should be changed. Changing it to a more unique name can boost your site’s security. Use the query below to change the default “admin” username:
[code]UPDATE wp_users SET user_login = ‘Your New Username’ WHERE user_login = ‘Admin’;[/code]
# Change your WordPress password
Need to change your WordPress password and can’t find where to do it in the admin panel? Use the query below to quickly make the change:
[code]UPDATE wp_users SET user_pass = MD5( ‘new_password’ ) WHERE user_login = ‘your-username’;[/code]
# Assign posts from one author to another
Say you want to transfer articles from one author to another. This is another time consuming and tedious process if you’ve got a lot of posts on your site. You can easily and quickly make this change, but first need to obtain the author’s ID by going to your user page in the WordPress admin panel. Click the user’s name to view their profile and then look at the address bar taking note of the “user_id”. Then, use the query below to easily transfer ownership from articles assigned to one author to another:
[code]UPDATE wp_posts SET post_author = ‘new-author-id’ WHERE post_author = ‘old-author-id’;[/code]
# Delete revisions
When you edit WordPress posts of pages, by default, it saves each revision so you can easily revert should something go wrong. This can cause use up your server resources when you have thousands of entries. Your database will grow significantly and will cause an increase in loop iterations, data retrieval and will affect page load times. To help optimize your WordPress database, delete old revisions that you don’t need anymore by using the query below:
[code]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'[/code]
# Delete post meta
Installing or removing plugins is a very common task for WordPress. Some of the plugins make use of the post meta to store data pertaining to the plugin. After you have removed the plugin, those data are still left inside the post_meta table, which will no longer be needed. Run the following query to clean up the unused post meta value. This will help to speed up and reduce the size of your database.
[code]DELETE FROM wp_postmeta WHERE meta_key = ‘your-meta-key’;[/code]
# Export all comment emails with no duplicates
Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.
[code]SELECT DISTINCT comment_author_email FROM wp_comments;[/code]
# Delete all pingbacks
Popular articles receive plenty of pingback. When this happens, the size of your database increases. In order to reduce size of the database, you can try removing all the pingbacks.
[code]DELETE FROM wp_comments WHERE comment_type = ‘pingback’;[/code]
# Delete all spam comments
If you have plenty of spam comments, going through each page to delete spam can be tedious and frustrating. With the following SQL query, even if you have to face deleting 500 over spam comments, it will be a breeze.
[code]DELETE FROM wp_comments WHERE comment_approved = ‘spam’;[/code]
- 0 = Comment Awaiting Moderation
- 1 = Approved Comment
- spam = Comment marked as Spam
# Delete unused tags
In a WordPress database, if you run a query to delete old posts manually from MySQL, the old tags will remain and appear in your tag cloud/listing. This query allows you to delete the unused tags.
[code]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;[/code]
# Find how many entries are spam
We look at the column
comment_approved. Here WordPress stores the type of the comment. We collect the amount of the different values with
COUNT(*) and put it into the variable
[code]SELECT `comment_approved`, COUNT(*) as amount
GROUP BY `comment_approved`
ORDER BY amount DESC
LIMIT 0, 30[/code]
comment_approved amount spam 12118 1 104 post-trashed 1
1 represents approved, real comments. We are already seeing: only 0.85% of all comments are spam. Unfortunately, this is a normal value. The mean part is: MySQL must always go through the entire table to find the real comments for the output in the published articles. With an index, an optimized query structure, this goes faster.
# Find which posts suffer from the most spam
The more popular a post is, the more spam it attracts. Some posts get almost never spam, others are downright overwhelmed. We get the 20 most spammed posts with this query:
[code]SELECT `comment_post_ID`, COUNT(*) as amount
WHERE `comment_approved` = ‘spam’
GROUP BY `comment_post_ID`
ORDER BY amount DESC
LIMIT 0, 20[/code]
comment_post_ID amount 1776 2299 2184 1840 143 1759 1366 1145 2377 960 2456 512 2536 497 683 486 1373 236 1402 200 1522 174 2458 161 1168 127 10473 121 647 116 2357 114 2831 111 2420 110 741 94
Now we could protect for these articles the comments with a registration or just close the comment form. Both solutions are not ideal.
# Find where the most spam comes from
[code]SELECT `comment_author_IP`, COUNT(*) AS amount
WHERE `comment_approved` = ‘spam’
GROUP BY `comment_author_IP`
HAVING amount &gt; 10
ORDER BY amount DESC
LIMIT 0, 30[/code]
comment_author_IP amount 18.104.22.168 450 22.214.171.124 386 126.96.36.199 287 188.8.131.52 268 184.108.40.206 218 220.127.116.11 181 18.104.22.168 157 22.214.171.124 153 126.96.36.199 148 188.8.131.52 147 184.108.40.206 140 220.127.116.11 117 18.104.22.168 109 22.214.171.124 91 126.96.36.199 88 188.8.131.52 82 184.108.40.206 80 220.127.116.11 80 18.104.22.168 73 22.214.171.124 63 126.96.36.199 63 188.8.131.52 55 184.108.40.206 54 220.127.116.11 54 18.104.22.168 51 22.214.171.124 48 126.96.36.199 48 188.8.131.52 46 184.108.40.206 46 220.127.116.11 46
There is so much spam from some IP addresses that we can lock them out safely. This is works in a
.htaccess, for example, like this:
# IP block list order allow,deny deny from 18.104.22.168 allow from all
Do you have any useful WordPress SQL queries you use?
I’d love to hear from you! Comment below with some useful WordPress SQL queries you use to update, optimize and manage your site.