WordPress uses a MySQL database to store even a single bit of information to make your website functional. As soon as your website grows, it will be harder to maintain the performance of your website. In this article, we will be listing 20 WordPress MySQL Query hacks to manage your website’s database.
WordPress stores every single bit of information into the database like pages, posts, comments, shortcodes, even your website’s configuration. WordPress provides an intuitive backend interface to manage your website – which is great.
Let’s assume you have a very old and high traffic website. You will probably have hundreds of posts stored within a database. Suddenly you want to make site-wide changes to all posts.
Making changes to each and every post manually by editing will be a nightmare and time-consuming. If you want to make these kinds of bulk changes to your website, then its time to get your hands dirty with MySQL database queries.
Before getting our hands dirty, let’s find out some basics of a WordPress database.
Introduction to WordPress Database
As said earlier, WordPress store all website information in the database even the structure of your website. This makes a WordPress website dynamic, as all information grabbed from database and HTML is generated in real time.
Every time a visitor access your website, the content, and structure of your website will be fetched from the database using MySQL queries. As we already know, WordPress is built using PHP language, PHP runs all those queries to communicate with the MySQL database.
All WordPress data is stored in tables. Tables act like a container where MySQL organize your data. There are different tables used in the WordPress database structure where the different type of data is stored into specific tables.
We have listed almost all of WordPress’s core tables below, so you will have a better understanding of WordPress’s table structure and know the purpose of each of them.
- wp_posts: All posts, pages, menus, and media attachments are stored in this table including custom post types.
- wp_comments: All comments on posts and pages are stored in this table.
- wp_postmeta: Meta information of all posts are stored into this table.
- wp_commentmeta: All comment’s meta information stored in this table.
- wp_users: Users data including passwords are stored in this table.
- wp_usermeta: This table store all users metadata.
- wp_options: All WordPress settings are stored in this table.
- wp_terms: Categories and tags are stored in this table.
- wp_termdata: Metadata for categories and tags are stored in this table.
- wp_term_taxonomy: Taxonomies data is stored in this table which is used to classifying your data.
- wp_term_relationships: This table links categories and tags to posts.
Above are the core tables to make any WordPress website functional, you might see more tables which are created by plugins or themes.
How to use WordPress MySQL Query?
To run any MySQL query you will need to access phpMyAdmin, which is a free tool to manage MySQL databases. You can find phpMyAdmin in your hosting cPanel if you are using a shared hosting.
When you open phpMyAdmin, select your WordPress’s database from the panel on left. It should be named with your domain name and suffix with wp_.
Make sure you select the right database if you are hosting multiple websites.
Backup Your Database
The first thing you have to do before running any MySQL query is to backup your database.
It is always advised to make a backup copy of your database before making any changes.
No matter how competent you are with MySQL, always backup your database. Any type of query will update or delete your database records which will not be recoverable.
The quickest and easier way is to export your database and save it on your computer somewhere safe.
Exporting WordPress Database
- Login to phpMyAdmin.
- Select the WordPress database you want to export.
- Click on the Export tab on the top panel.
- In the format drop-down, choose SQL to export your database as a .sql file.
- Click on Go and a copy of the database will be downloaded to your computer.
Importing WordPress Database
- To restore the database, click on the Import tab.
- Leave the format as SQL.
- Browse to file where you saved the backup copy of your database.
- Click Go.
20 WordPress MySQL Query Hacks
Now you know how to backup your database and know the purpose of each table, let’s explore some MySQL queries which can help you to make some bulk changes.
All you have to do is, copy and paste the below queries with changes you want to make into SQL query section of phpMyAdmin.
Click on the SQL tab on the top panel, and you will be presented with a simple editor where you can paste the MySQL queries. After pasting simply click on Go button.
1. Change WordPress URL
WordPress store base URL of your website in the database’s wp_options table. If you have moved your website from one domain to other then you might want to update the base URL.
Your website won’t function without updating the base URL of your site. You can run below query to update base URL.
2. Updating Image Path
You can load all WordPress images from a cloud storage like Amazon CloudFront’s content delivery network. After updating your CNAME record you can run below queries to update the images path to your new location.
Below two queries will update the all images on your website.
3. Add a New Field to Posts
You might want to add a new field to your posts or pages in your WordPress installation. Below query will add a new field customField to all posts. If you want to change field name simply replate customField in below query.
To add a new field to WordPress’s pages replace the post with a page in the last section of the query.
4. Delete Post Meta Data
If you have a bit old website and you have used a lot of plugins in the past. Then you might want to remove all leftovers of plugins you are not using anymore.
All WordPress plugins use wp_postmeta table heavily to add data. After uninstalling the plugin, all cluttered data will might be left in this table. You can clean up the table to optimize the database using below query.
Change meta key meta-key-you-want-to-remove to the one you want to remove.
5. Get Unused Tags for Deleted Posts
If you delete a post, WordPress won’t automatically remove unused tags from the database. This query will return all unused tags left over in the database.
6. Reset Password
If you have forgotten your WordPress password and want to reset from the database, below is the query to rescue you from such a situation.
Remember to add your new password and username in the query.
7. Change Default Username
WordPress creates a default username as Admin, which is a huge security risk. If anyone gets access to your WordPress admin section using this username, they will have full control on your Website.
You can change your default username by below query.
8. Delete Revisions
If you are updating your posts frequently, WordPress save the revision of your posts on every change. Which slow down your website.
You can use the below query to clear all WordPress revisions.
9. Re-Assign User to Posts
If you want to assign other author’s article to your username then below query will assign all posts to your username.
This query can be used when you want to take the ownership of legacy articles.
10. Delete All Unapproved Comments
Want to remove all unapproved comments from your website, use below query.
11. Delete Pingback
Your most popular can create tons of pingback and you might want to get rid of them. Below query will delete all pingbacks from your website.
12. Delete Comments from a Specific URL
Submitting spammy comments on a WordPress website is fairly easy if there are not proper plugins in place. It’s quite possible, you will be getting a lot of spammy comments from the same domain.
You can clear all spammy comments by running the following query. The % means you can delete all comments coming from the given domain.
The below query will delete all spammy comments coming from the same domain.
13. Switching WordPress Posts to Pages
Changing a page to post or post to a page is fairly easy. Run the below query and it will change all post to pages or vice versa.
To change the post type of a particular post you have to enter the post ID in the where clause.
14. Removing Shortcodes
WordPress’s shortcodes provide a way to reuse the code again and again. Shortcodes are convenient, but if you have decided to remove all shortcodes using below query.
Replace “shortcode-name” with the name of shortcode you are trying to remove.
15. Find All Posts by a Category
If you want to all posts by a category using MySQL query, you will need the category ID to pass it to the query.
The first query in below snippet will help you to find the category ID, simply replace “General” with the category name you are looking for.
Then in the second query replace 999 with the correct category ID and query will return you all posts attached to given category.
16. Finding Posts by Month
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.
You can add post_author to the WHERE clause or specifying a particular MONTHNAME and YEAR.
17. Find Edited Posts
You can find recently modified posts using MySQL query. The following series of queries will help you to find posts that have been recently modified for specific time periods.
18. Find and Replace Content in Posts
Sometime you will be in a situation where you want to replace a certain block of content with the most updated version. Use below query to easily achieve this.
19. Update URLs in Posts Content
To change the URLs within your content, you can use below query to replace all old URLs to the new ones.
20. Disable Comments on Old Posts
Changing the status of comments in bulk is very easy. Use below query to change the statuses of comments by setting comment_status to either open, closed or registered_only.
Understanding how WordPress database works can give you more control on your WordPress website and help you to complete certain tasks rapidly instead on relying on WordPress Plugins for common tasks.
Messing with your database or making direct changes can be dangerous if you don’t know what you are trying to achieve. It’s vital and always recommended that you do make a backup of your database before making any changes.