As active users of WordPress know that WordPress is driven by a MySQL database. However, if you dont then this post is very helpful for you. MySQL is a free relational database management system (RDBMS)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 wants to change some information in WordPress, going through each record is very time consuming and errors may occur. 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
Backup your database. It is a good practice to always backup your database before making any major changes. This will 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.
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.
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.
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.
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.
UPDATEwp_users SETuser_login = 'Your New Username'WHEREuser_login = 'Admin';
Ever wanted to reset your password in WordPress, but cannot seem to use the reset password section whatever the reason?
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.
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.
DELETEa,b,c FROMwp_posts a
LEFTJOINwp_term_relationships b ON(a.ID = b.object_id)
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.
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.
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.
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.