Did you know that WordPress powers two out of every five websites on the web? The WordPress database is what provides the foundation for these millions of websites. You’ll agree that knowing about the foundation of your WordPress website is important, right?
Understanding what the WordPress database is and how to use it will help you to get the best use out of your website, opening up possibilities you never knew existed. In this post, we’ll talk you through what a WordPress database is and how it works to keep your website up and running.
What is a WordPress Database?
A WordPress database is a storage of all your website data using MySQL open-source system. It stores all information about a WordPress website, including (but not limited to):
- posts, webpages
- user data and comments
- plugins and theme-related data
- website configuration settings
WordPress uses the MySQL database management system. This open-source relational database management system(RDBMS) is the most popular option for creating databases because it works with various open-source applications.
The database stores all of this information in tables, rows, and columns. Although it has a set default MySQL structure, the WordPress database is dynamic, allowing you, the admin, to add, modify, and delete information.
WordPress uses PHP and the SQL query within the PHP markup to create, read, update, and delete data from the MySQL database.
In summary, everything that makes up your website is housed within its database, so it’s a crucial part of any website.
How the WordPress Database Works
Before installing WordPress, you must first have a MySQL database on your web server. Fortunately, most WordPress hosting providers already have MySQL included in their packages.
During WordPress installation, you’ll be required to provide a database name, host, username, and password. This information is stored in a configuration file called wp-config.php — which we’ll discuss soon — along with other essential files.
WordPress sends a MySQL query to the database when you need to create, alter, read or delete data from the database.
So, when a person visits your website, their browser sends a request to your website’s server. The server then transfers the data to the visitor's IP address and displays the requested information.
How the WordPress Database Organizes Data
WordPress database organizes data in tables, rows, and columns. This arrangement is best because it allows information to be quickly and easily accessed.
A new WordPress database usually includes the following tables:
- wp_commentmeta: This table stores meta-information about the comments posted on a WordPress website. It has four fields, namely:
- wp_comments: This table stores WordPress comments and their properties. Some of these properties include comment author name, comment, URL, email, and others.
- wp_links: This holds information related to links entered into the Links feature of WordPress.
- wp_options: An important table, wp_options contains WordPress site-wide configuration settings like the site URL, admin email, time format, etc. Additionally, plugins use this table to store plugin settings.
- wp_postmeta: This table houses meta information about website assets like pages, posts, and custom posts. Plugins also use this table to store plugin data.
- wp_posts: This table contains content types like revisions, custom post pages, revisions, etc.
- wp_termeta: This table stores metadata.
- wp_terms: The categories for posts, links, and post tags are stored here.
- wp_term_relationships: The information stored in this table helps WordPress manage relationships between post types and the wp_term table.
- wp_term_taxonomy: This table contains data that helps WordPress determine which terms are categories or tags.
- wp_usermeta: As the name implies, this table contains meta-information about the registered users on your website.
- wp_users: This table stores user information like username, email, password, etc.
All of the tables above contain individual columns and fields that house more specific types of information.
You can find the full complement of details in the official WordPress Database description. WordPress also offers a handy visual diagram of the default database structure, shown below.
How To Create a WordPress Database
There are several ways to create a WordPress database and user account if you’re installing WordPress on your web server.
Two of the more popular ways are using cPanel and phpMyAdmin. Let’s walk through using both of these tools.
Creating WordPress Database with cPanel
Follow these instructions if your hosting provider gives you the cPanel hosting control panel:
1. Log in to your cPanel.
2. Click the MySQL Database Wizard icon under the Databases section
3. Enter the database name in the Create Database Users area, and click Next Step.
4. In Create Database Users, enter the database user name and password. Next, click Create User.
5. In Add User to Database, click the All Privileges checkbox. Then click Next Step.
6. Store the values of hostname, username, database name, and the password you used by writing them in a secure place.
You can find a more detailed set of instructions for using cPanel in the Using cPanel documentation.
Creating WordPress Database with phpMyAdmin
phpMyAdmin is a free software tool that lets you easily manage the MySQL database of your website. Some web servers have phpMyAdmin pre-installed, so you don’t have to install it yourself.
Before you use phpMyAdmin, we highly recommend you make a backup of your WordPress site. A backup allows you to restore your website to its previous state if you make accidental changes that damage your website.
After creating a backup, follow these instructions to create your WordPress Database:
1. Create a database relating to your website if it doesn't already appear in the Database dropdown menu o the left. Enter the chosen database name in the Create database field and choose the best configuration for language and encoding.
2. Select the phpMyAdmin icon to return to the main page, then select Users. You can create users from this page. To add a new user, follow the following instructions:
- Click Add User Account.
- Enter a name in the User name field.
- Choose a secure password and input it into the Password field.
- Leave the options under Global privileges at default.
- Select Go.
- Return to the Users screen and click the Edit privileges icon on the new user tab.
- In the Database-specific privileges section, click the new database under the Add privileges to the following database dropdown, and click Go.
Aside from using cPanel and phpMyAdmin, you can also create a database using Plesk, LPCP, MySQL Client, and DirectAdmin. WordPress provides detailed instructions on using each of these tools.
How to Optimize a WordPress Database
As with all tools, your WordPress database requires maintenance. Database optimization is one way to keep your database in the best shape.
If you don’t optimize your WordPress website database, you’ll notice your site getting slower over time. Fortunately, optimizing your WordPress database is a relatively simple process. Follow these steps:
1. Log into phpMyAdmin and click on Databases.
2. Select the database you want to optimize.
3. Next, click the Check All button at the bottom of the page.
4. Navigate to With Selected drop-down menu and select Optimize Tables.
5. The system will automatically optimize all tables selected.
For more in-depth instructions, see our full guide to optimizing your WordPress database.
Understanding the WordPress Database
The thought of editing your website’s WordPress database might seem a daunting task, especially if you’re not a developer.
However, knowing how to interact with the database and optimize it will put you ahead of the majority of WordPress users. You’ll run a faster website, and you may even be able to diagnose and fix problems with your WordPress content as they arise.