13 Useful WordPress SQL Queries You Wish You Knew Earlier
WordPress is driven by a MySQL database. This is something active WordPress users would know. However, if you only just read about it here from us, here’s what you should know. MySQL is a free relational database management system available in most web hosting services. All of the WordPress data like the posts, comments, categories, and settings are stored within the MySQL database. If you wish to read up more about MySQL, you can take a look at WordPress’s Database Schema.
For example, if you needed to change some information across the board in WordPress, going through each record is very time consuming and prone to human error. By simply executing a SQL query against your WordPress database, you can make the necessary changes quickly and efficiently.
Shown below are some SQL queries that can be of great assistance to you when using WordPress.
Backup your WordPress Database
Before you proceed with any changes, be sure to backup your database. It is a good practice to always backup your database before making any major changes. This ensures that even if anything were to go wrong, you would still be able to restore it.
You can download WP-DB-Backup or WP-DBManager plugin to backup your database through your WordPress admin panel. Alternatively, you can backup your database through phpMyAdmin manually.
If you decide to backup your WordPress database manually, follow these steps:
- Login to your phpMyAdmin.
- Select your WordPress database.
- Click on Export at the top of the navigation.
- Select the tables you want to backup, or select all tables to backup the whole database.
- Select SQL to export as .sql extension.
- Check the "Save as file" checkbox.
- Choose compression type, select gzipped to compress the database to a smaller size.
- Finally click Go, and a download window will prompt you to save your backup database file.
13 SQL Queries for WordPress
An easy way to run any of the queries below is to use phpMyAdmin. phpMyAdmin is one of the most common database look-up and editing tool. Almost all the web hosting services’ control panels provide this. There is also WordPress SQL Executioner - a WordPress plugin that allow you to execute your SQL query.
To use phpMyAdmin follow the steps below:
- Login to phpMyAdmin panel and select your WordPress database.
- Click on the SQL tab which will bring you to a page with a SQL query box.
Once you see the SQL query box, like the image shown below, you can run your SQL query there.
Note: All queries are direct SQL queries and will assume the standard table prefix 'wp_' for tables. If you are using a different table prefix, please modify queries accordingly.
Change Siteurl & Homeurl
WordPress stores the absolute path of the site URL and home URL in the database. Therefore, if you transfer your WordPress site from the localhost to your server, your site will not load online. This is because the absolute path URL is still pointing to your localhost. You will need to change the site URL and the home URL in order for the site to work.
Solution:
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';
Change GUID
After you have migrated your blog from the localhost to your server or from another domain to a new domain, you will need to fix the URLs for the GUID field in wp_posts table. This is crucial because GUID is used to translate your post or page slug to the correct article absolute path if it is entered wrongly.
Solution:
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');
Change URL in Content
WordPress uses absolute path in the URL link instead of a relative path in the URL link when storing them in the database. Within the content of each post record, it stores all the old URLs referencing the old source. Therefore you will need to change all these URLs to the new domain location.
Solution:
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');
Change Image Path Only
If you decide to use Amazon CloudFront as your Content Delivery Network (CDN) to offload the delivery of images from your server. After your have created your CNAME record, you can use the query below to change all the image paths in WordPress to load all your images from Amazon CloudFront.
Solution:
UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsiteurl.com', 'src="http://yourcdn.newsiteurl.com');
You will also need to update the GUID for Image Attachment with the following query:
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://yourcdn.newsiteurl.com') WHERE post_type = 'attachment';
Update Post Meta
Updating Post Meta works almost the same way as updating the URL in post content. If you have stored extra URL data for each post, you can use the follow query to change all of them.
Solution:
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://www.oldsiteurl.com','http://www.newsiteurl.com');
Change Default "Admin" Username
Every default WordPress installation will create an account with a default Admin username. This is wide spread knowledge, everyone who uses WordPress knows this. However, this can be a security issue because a hacker can brutal force your WordPress admin panel. If you can change your default “Admin” username, you will give your WordPress admin panel additional security.
Solution:
UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin';
Reset Password
Ever wanted to reset your password in WordPress, but cannot seem to use the reset password section whatever the reason?
Solution:
UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';
Assign all articles by Author B to Author A
If you want to transfer the articles under Author B to merge with those under Author A, it will be very time consuming if you do it article by article. With the following SQL query, you can easily go through all the records and assign articles by Author B to go under Author A.
You will first need to obtain the author ID of both authors by going to your Author & User page in your WordPress admin panel. Click on the author’s name to view their profile. At the address bar, look for "user_id". That is the author ID information we require.
Solution:
UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';
Delete Revision
When you are editing an article in WordPress, there will be many revision copies being saved. This is a waste of resources because excessive revision records can increase the burden of the database. Over time, when you have thousands of entries, your database will have grown significantly. This will increase loop iterations, data retrieval and will affect the page loading time.
Solution:
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'
Source from: Lester chan
Note: Bear in mind that all revisions from each post will be deleted, including all of its meta data.
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.
Solution:
DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';
Export all Comment Emails with no Duplicate
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.
Solution:
SELECT DISTINCT comment_author_email FROM wp_comments;
Once you have the result, under Query results operations, select export to export all the emails in phpMyAdmin.
Delete all Pingback
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.
Solution:
DELETE FROM wp_comments WHERE comment_type = 'pingback';
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.
Solution:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
- 0 = Comment Awaiting Moderation
- 1 = Approved Comment
- spam = Comment marked as Spam
Identify 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 identify the unused tags.
Solution:
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;
Start Querying!
If you need to do some maintenance and clean up or move WordPress across domains, the above SQL queries can be useful and help speed things up for you. If you have any additional handy SQL queries for WordPress, please share it with us below.
Thank you for sharing ;)
Maybe this code useful, grab current week posts :
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$thisWeek = date('W');
$newsposts = query_posts('cat=-44&showposts=' . $limit . '&paged=' . $paged .'&w='.$thisWeek);
OMG, so WordPress is that much popular nowadays that instead of "SQL queries (to manage WordPress by hand)" the article is titled "WordPress queries"? (... with phpMyAdmin in opening illustration?)
Half of the article is about plain old simple text replacement, and the other half... "Delete Revision"? Come on, you wordpressers have a brazillion of plugins for all happenstances from tweeting to sneezing and - what - no plugin to delete revisions? Surprising :)
"Identify Unused Tags" although is a very nice and useful query. Let me guess - the rest is so the post would not consist of just one snippet? Even so, thanks for the up - probably this will come in handy at some point in the future.
Good post. Bookmarked.
This is a very useful post and can you please write a detailed post on how to back up wordpress database using php my admin with using photo, as it make it easy for us.
Wow thank you for this post. Very very useful info.
I was wondering if you know of any magic SQL strings for helping move a WP site from a sub directory site.com/v2 to the main directory site.com/ ?
Would updating the siteurl work for this?
thanks
Jon Spooner
Yes it will.
Of course with PhpMyadmin you don't even have to run queries, you can just edit the fields directly. Sometimes this is the easiest way to do things!
Very good tutorial :) Can I translate it into French for my blog (xoodeo.com) ?
Sure.. a link back to this article will be great ;)
Of course for the link :D Thanks a lot !
hi!
By which version can use it?
2.8? or 2.9?
Hi, my name is Marcos, I'm from Mexico and I wonder if I can use part of this blog to post it into my blog. My blog is focused in design and programming and I 'd like to post some of the code, of course, I 'd put the link to the original article
:)
Sure. ;)
this one is fun too
MYSQL="mysql -h HOST -u USERNAME -pPASSWORD -D DB_NAME"
$MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop tabl `" $1 "`;"}' | $MYSQL
unset MYSQL
just kidding of course, it will delete all tables!
Good post. twitted
Nice collection tips. Thanks
RT'd. Great tips. Thank you so much!
Excelent,
really great collection
Interesting collection of queries, thank you. One thing as I am reading your content, if you could add some padding-left because the content goes all the way to the edge of my screen.
Just a heads up.
Thank you for the feedback.
A question from an ordinary user - that is, I am not a programmer.
When I download my backup and unzip it, I have a sql file which I can read as text using TextPad.
Is there some way to read the contents as a GUI interface?
In other words, can I recreate the blog and its functionality on my desktop?
Also is there some way that I can get a simple list of the post headers?
Any comments would be much appreciated ... but please keep them very simple.
Thanks in advance
Peter
First you'll need to install MySQL on your local computer.
Next, import the sql file you downloaded into your localhost through phpMyAdmin.
To get the post headers, run this query:
SELECT post_title FROM wp_posts WHERE post_type = 'post';
"can I recreate the blog and its functionality on my desktop?"
Yes, Peter, you could do that by installing software that emulates web-server on your computer. There are several popular software packages that do that: WAMP, XAMPP and others. Here's the link to a really nice and detailed tutorial on how to install WordPress locally:
http://sixrevisions.com/tutorials/web-development-tutorials/using-xampp-for-local-wordpress-theme-development/
You could then import your SQL-file from a live server into local MySQL using PHPMyAdmin.
Also note, that you will have to change the siteurl of your site from "something.com" to "localhost". Here's a tutorial to do the opposite (from localhost to something.com)
http://jestro.com/convert-local-wordpress-xampp-installation-into-live-site/
but it is easy to figure out how to do it the other way.
Of course, if you have a custom theme and images on your live server you will have to just copy the whole folder with WordPress and put it in your htdocs folder in the first place.
Alas, couldn't keep the comments very simple, but it's just because the matter itself isn't :)
Very useful post for me. Thanks!
Thanks alot for sharing, if been looking for a summary like this for ages.
Again thanks for sharing
Some really useful queries, especially for a wordpress and phpMyAdmin n00b such as myself.
Thank you, keep up the good work :)
Great, this help me a lot for changing my subdomain. Really useful, thanks!!!
PD: Can I translate some parts of your post into my blog? I will put the link to your article :)
Thanks for sharing! Found the content to be very useful! :)
How can I query any user, and change them from a contributor to an administrator..
"WordPress SQL queries"? Ehm, no, simply SQL queries to run in MySQL.
And no, WordPress is not "driven" by MySQL. WordPress stores data in MySQL databases. WordPress could be said to be "driven", or preferrably, run with PHP.
I do think this matters.. Otherwise, thanks for the useful snippets.
Thanks for sharing! Found the content to be very useful
I found an issue with your queries, the post meta change will break if your old url and new url are not the same character length. as it is a serialized array if you change the length you have to change the number specified for the string as well
Thanks for this info, I am working on a new wordpress blog and these SQL Queries are proving to be very useful, thanks again :)
good topic
thx. for sharing all this information is very usefull for me that just learning to blog using wordpress.
thank you
really worth post, I need some of queries..
Identify 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 identify the unused tags.
Solution:
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.coun
How to Delete them ?
As it is a serialized array if you change the length you have to change the number specified for the string as well
WoOoOoOoW
nice share ,
plz put more sql for wordpress
really amazing post
thanks
good topic
thx
Never thought about changing the default 'admin' name :) Nice one. Will use it. Thanks for sharing.
Very nice and useful queries...
Do you know the query to mass import taxonomies to db?
Adding a few hundred taxonomies one by one will kill me...
Maybe with php&mysql I can do this.
Nice collection, some of them I've know but the other are new for me, thank for that
Just a quick note that the query for replacing image URLs will also change the URLs for any JavaScript which might be embedded in the post. Probably not that common a scenario, but maybe worth noting. Nice post!
Thanks for sharing. It is very useful for me,,,, i need some revisions of my site so your article is very useful for me.