The WordPress Database Demystified

Shaumik Daityari
Tweet

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.

The WordPress Database

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_comments 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.

WordPress database graphical view
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!

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Oscar Blank

    I have a like/hate relationship with WordPress. I like that I can make sites really fast. I hate that the code is written so poorly, and the database lacks any optimization or attempt at normalization. This is the kind of application that would get an F if it were graded by a computer science professor. It’s the perfect example of how not to code, and how not to design a database.

    Even more retarded is the way WP forces you to write custom queries. Sure, if you want to do a simple query, there’s nothing holding you back, but try to query for custom post types where you want to filter by a taxonomy and sort by a custom field. It’s way harder than just writing the SQL. They take something that should be very simple and turn it into a chaotic mess.

    Nevertheless, most of the websites I make these days are with WordPress. Typical customers expect cheap websites, so to stay competitive with the recently graduated high school student, one must be able to put together a website that is sub $1000 in less than a day …

    • http://dada.theblogbowl.in/ Shaumik Daityari

      I understand your concerns regarding the optimizations but there are many caching plugins that are pretty effective these days. Although for small and upcoming sites, I would prefer just page caching.

  • http://dada.theblogbowl.in/ Shaumik Daityari

    Glad that you liked it :)

    And yes, the delete queries can be harmful (especially if you do some typo!). Therefore, you should be extra careful!

  • http://dada.theblogbowl.in/ Shaumik Daityari

    Thanks :)

  • http://www.zacksdomain.com/ Zack Wallace

    I wasn’t aware that you could set a new password with just an MD5. Please tell me WordPress isn’t doing just an MD5 for passwords? No Salt or other advanced hashing?

  • http://dada.theblogbowl.in/ Shaumik Daityari

    Personally, I have never felt the need to update the wp_options table though. You could always do it through your dashboard. But yes, if they are serialized, it’s not a good idea to change their values directly.

  • Lykos

    I have a question regarding the wp-opitons table. Since wp-opions stores all the settings of the site how can I fetch a specidic item like the site title or the timezone? To be more specific what I’m looking for is how the sql query would be in this case in order to fetch an item

    • http://dada.theblogbowl.in/ Shaumik Daityari

      Hi,

      One option would be to run “SELECT * from wp_options;” to list all the entries and check what option_name corresponds to what. Or, if you know the title of your site, and you want to search the title of some other site, you could check the value of “option_name” that stores the title on your known table.

      For a comprehensive list, you could check this.
      http://codex.wordpress.org/Option_Reference

      • Lykos

        Hi and thank you for your reply. Well, I’m actually focusing on the db table structure, whether its the best way to go in order to do something similar to a custom application.

        If you to a SELECT * FROM `wp-options` you will fetch all data from option_id, option_name and option_value columns. So in order to display them all i assume that you have to loop through those 3 columns and get the site name or the date format. But how you could create a query to get only the site name or the date format from this table? you can’t do something like this SELECT `site_name` from `wp-options` , as the site name is not a column, right?

        Also how you could do all crud functionality, in general, on a table like this?

        Hope i made my self clear.

        • http://dada.theblogbowl.in/ Shaumik Daityari

          If you want only site name from a query, you need to know the value of option_name, which can only be determined by observing the value in the table first (and then you can use the name in the column name later, of course).

          I would also suggest that you don’t perform crud options on this table unless absolutely necessary. And even if you do, make proper backups before you do so. I would suggest that you avoid changing the values of the existing values as they can be modified by the admin, but if you want to add options, you can do so by using your own option_name.