The WordPress Database Demystified

    Shaumik Daityari
    Shaumik Daityari
    Share

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

    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!

    Frequently Asked Questions (FAQs) about WordPress Database

    What is the Importance of a WordPress Database?

    The WordPress database is the heart of your WordPress site. It stores all the critical information like posts, pages, users, comments, and settings, making it an essential part of your website. Without the database, your website would not function correctly as it wouldn’t have access to the necessary data. Understanding how the WordPress database works can help you manage your site more effectively, troubleshoot issues, and even optimize your site’s performance.

    How Can I Access My WordPress Database?

    You can access your WordPress database through a tool called phpMyAdmin, which is typically provided by your web hosting company. Once you log into your hosting account, look for a section called “Database” or “MySQL Databases.” There, you should find a link to phpMyAdmin. After clicking on it, you’ll be able to see your WordPress database and manage it directly.

    How Can I Optimize My WordPress Database?

    Optimizing your WordPress database can help improve your site’s performance. You can do this by using a plugin like WP-Optimize or WP-Sweep. These plugins help you clean up your database by removing unnecessary data like revisions, drafts, spam comments, and transient options. You can also optimize your database manually using phpMyAdmin, but this requires a good understanding of how databases work.

    How Can I Backup My WordPress Database?

    Backing up your WordPress database is crucial to prevent data loss. You can use a plugin like UpdraftPlus or BackWPup to automate the backup process. These plugins allow you to schedule backups, choose what to backup, and even store your backups on cloud services like Dropbox or Google Drive. You can also backup your database manually using phpMyAdmin, but this can be a bit more complex.

    What is the Structure of a WordPress Database?

    A WordPress database is made up of tables, and each table contains specific types of data. For example, the wp_posts table contains data related to posts, pages, and custom post types. The wp_users table contains data about the users on your site. Understanding the structure of the WordPress database can help you manage your site more effectively and troubleshoot issues when they arise.

    How Can I Repair a Corrupted WordPress Database?

    If your WordPress database gets corrupted, you can repair it using a feature built into WordPress. To do this, you need to add the following line to your wp-config.php file: define(‘WP_ALLOW_REPAIR’, true);. After doing this, you can visit www.yoursite.com/wp-admin/maint/repair.php to repair and optimize your database.

    How Can I Change the Prefix of My WordPress Database Tables?

    Changing the prefix of your WordPress database tables can help improve your site’s security. You can do this by using a plugin like Change DB Prefix or manually by editing your wp-config.php file and your database tables via phpMyAdmin. However, this should be done with caution as it can break your site if not done correctly.

    How Can I Move My WordPress Database to a New Server?

    Moving your WordPress database to a new server can be done by exporting your database from your old server using phpMyAdmin and then importing it to your new server. You also need to update your wp-config.php file with the new database information. There are also plugins like Duplicator that can simplify this process.

    What is a MySQL Database in Relation to WordPress?

    MySQL is the database management system that WordPress uses to store and retrieve all your website data. It’s a powerful system that allows WordPress to store everything from your posts and pages to your users and settings. Understanding MySQL can help you manage your WordPress database more effectively.

    How Can I Secure My WordPress Database?

    Securing your WordPress database is crucial to protect your site from hackers. You can do this by using strong usernames and passwords, changing your database table prefix, keeping your WordPress site updated, and using a security plugin like Wordfence. Regular backups are also an important part of a good security strategy.