The WordPress Database Demystified

Since it was released over a decade ago, WordPress has become the most popular CMS choice for beginners and experts alike. Although the installation of WordPress, and the addition of data does not require any knowledge of the underlying structure, you might find yourself in a situation where some knowledge of the database is required.

Although the favorite choice of users when it comes to working with databases is often phpMyAdmin, we will focus on raw SQL queries, keeping in mind that everyone may not be comfortable with phpMyAdmin (you have an option in phpMyAdmin to run raw SQL queries too!).

Exploring the Database

As you probably know, WordPress uses MySQL. To log into MySQL, run the following in your terminal:

mysql -u [username] -p -D [database_name]

Once you have logged into the WordPress database in MySQL, you can check the tables that WordPress has created by running the following:

show tables;

To check the structure of any table, run:

desc [table_name];

Note that I would be describing the tables that are created by WordPress on a fresh installation. Installing new plugins may create new tables, which I will not be covering in this post.

Also, I will assume that your table prefix is “wp“. In case you have used a different table prefix, replace “wp” by your prefix in the table names.

I will explain the tables in a logical order, rather than the alphabetical order that you will find in most tutorials.

wp_options

The wp_options table stores all the settings of your WordPress site like title, tagline and timezone. All the options that you set in Settings in your Dashboard are stored in this table.

wp_users, wp_usermeta

As the name suggests, wp_users stores the list of all the registered users on your WordPress site. It contains the basic information of a user like login, password (which is encrypted), email, time of registration, display name, status and activation key (if required).

wp_usermeta stores the meta data (or ‘data about data’) of the users. For instance, the last_name of a user is saved in the wp_usermeta table, rather than the wp_users table.

There are two fields in this table that you should know about — meta_key and meta_value. Plugins can store custom meta data values about users in the meta table by using new meta_key values.

wp_posts, wp_postmeta

wp_posts stores all the post related data of your website. All the posts (and pages), and their revisions are available in the wp_posts table. Even navigation menu items are stored in this table.

The types of entries (posts, pages, menu items or revisions) are distinguished by the post_type column in the table. We will see later in this post how we can use queries in this table to our advantage.

wp_postmeta, just like the user meta data table, contains the meta data about posts.

If you use an SEO plugin, all the meta tag data generated for posts are stored are stored in this table.

wp_terms, wp_term_relationships, wp_term_taxonomy

Categories and tags for posts, pages or links are stored in the table wp_terms. A column that this table contains is a slug — which is a string that uniquely identifies the term, thereby used in the URL for the term. This helps in SEO as the Google Bot searches the URL for search terms too.

wp_term_relationship links these terms to objects (posts, pages or links). It serves as a map between these objects and the terms.

wp_term_taxonomy describes the terms in details. You can imagine it as meta data of the terms for simplicity, just that a plugin can not add custom values to this table.

wp_comments, wp_commentmeta

wp_comments stores the comments on your posts and pages. This table also contains unapproved comments and information about the author of the comments and nesting of comments. wp_commentmeta contains meta data about comments.

An important thing to note is that if you are using a third party commenting system like Disqus, your comments won’t be stored in your WordPress database, but in the servers of the commenting service.

wp_links

This table contains information about custom links added to your site. It has been deprecated, but can be enabled again by using the Links Manager plugin.

Here is the graphical view of the database, with tables linking to each other.

Source: WordPress.org

Using SQL to Your Advantage

Structured Query Language (SQL) is a computer language that is used to manage data in relational database management systems like MySQL. You can retrieve or manipulate data from database tables using SQL. We will try to use some queries that help us in doing certain tasks that are otherwise very difficult, or impossible, to do with the WordPress dashboard.

A Word of Caution

If you plan to execute the SQL queries that I am going to discuss, you should know that the changes they make are irreversible. Therefore, you must use BEGIN, COMMIT and ROLLBACK.

If you BEGIN a session before you execute the a command, you can always go back to the state before the command by using ROLLBACK.

BEGIN;
// Some Query
ROLLBACK;

If, however, you want the changes to stay, you can COMMIT the changes, and they become permanent.

BEGIN;
// Some Query
COMMIT;

You could also take a backup of your WordPress database either using a database dump or a plugin like the DB Backup before running these commands.

Now, let us look at a few tricks that we can use to make our work easier.

Change Default Usernames

You probably know that once you create a user, WordPress doesn’t allow you to change the username. However, you can do that through SQL by running a simple command.

UPDATE wp_users SET user_login = '[new_username]' WHERE user_login = '[old_username]';

The default administrator username in WordPress is ‘admin’ and because of its popularity, hackers often try this username. For security purposes, it’s advisable to change it to something else.

UPDATE: This worked at the time of writing, but doesn’t since recent updates. Please see the comments below for more information.

Change Your WordPress Password

Imagine you are the admin and you forget your password. You can easily change it, if you have access to the database.

UPDATE wp_users SET user_pass = MD5( '[new_password]' ) WHERE user_login = '[username]';

We need to use MD5 because the passwords are not stored in clear text format, but are encrypted.

Change Post Authors in Bulk

If you want to transfer posts from one author to another, you could try it with a simple SQL query.

UPDATE wp_posts SET post_author = [new_author_id] WHERE post_author = [old_author_id];

You need to first check the ID of the author from the wp_users table. However, if you are lazy, you can try this query, which looks complex, but doesn’t require you to check the author ID.

UPDATE
    wp_posts
SET
    post_author = (SELECT ID FROM wp_users WHERE user_login = '[new_author_login]')
WHERE
    post_author = (SELECT ID FROM wp_users WHERE user_login = '[old_author_login]');

Dealing with Spam Comments

If you do not use an external service for handling comments, chances are that you get a lot of spam comments. If you have a lot of spam, you could try using Akismet to keep a check on the spam. However, if you already have a lot of spam, you could try some SQL queries on them.

You can delete all spam comments in one go using the following:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

If you want to check where the spam comments are coming from, you can try this:

SELECT   comment_author_IP as ip_address, COUNT(*) AS count 
FROM     wp_comments
WHERE    comment_approved = 'spam'
GROUP BY ip_address 
ORDER BY count DESC

It will show you a list of how many spam comments came form each IP address.

Delete All Post Revisions

As I explained earlier, wp_posts saves the current version of a post whenever you click ‘Save’. Therefore, if you clicked ‘Save’ ten times while writing a post, there would be ten iterations of the post on your database. All revisions have the post_type as ‘revision’.

If you are curious about how many revisions you have generated so far, run the following. I am sure that the number will be big.

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

If you want to delete all such revisions of published posts, run the following command:

DELETE p, t, m FROM wp_posts p
LEFT JOIN wp_term_relationships t ON (p.ID = t.object_id)
LEFT JOIN wp_postmeta m ON (p.ID = m.post_id)
WHERE p.post_type = 'revision'

The reason I used LEFT JOIN is to remove the meta data and its connection with tags and categories.

Update Links in Posts

If you are moving to a new domain, chances are that there are many links in your posts, linking to your old domain. Changing them manually can be a tedious job. However, a simple SQL command may be used for this purpose.

UPDATE wp_posts SET post_content = REPLACE (post_content, '[old_domain]', '[new_domain]');

This query searches for occurrences of your old domain in the post_content column of the table and replaces them with your new domain.

Conclusion

With this, we come to the end of this tutorial. We hope that this post changed your view of how WordPress works in the background and helped you develop a clearer understanding of the database structure. Even though there are plugins for everything nowadays, you should think twice before installing all of them, especially when you have an easier and faster alternative.

Do you know any other SQL tricks for WordPress? Did we miss something important? Do let us know in the comments below!

Frequently Asked Questions (FAQs) about WordPress Database