WordPress database is where all of the necessary website data is stored. Not just the basic information like usernames and passwords but posts, pages and comments, even the website theme and WordPress configuration settings. Today we will take a look at why and how you should manage the WordPress database. Buckle up and enjoy the ride.
Why WordPress Needs a Database
Let’s look at what is a database first. As briefly stated earlier, a database can be a life-saving option for us to store and retrieve valuable data.
A database is a structured set of organized data. In the IT field, database management systems (DBMS) refer to software that interacts with a user, data, and retrieve and store data. It is pretty much the same when you save pictures or videos in your computer drive, or the business card binder if you prefer old-school card management. Having a personal database to access that data is very helpful.
There are several ways to use and access the database. WordPress uses MySQL for its database management system. MySQL is open-source software, and it is responsible for managing the components of a WordPress database such as user data, user meta, posts, comments, and so on. We will discuss it a little bit later.
WordPress uses PHP, using SQL query within the PHP markup, as the language of instruction to CRUD (Create, Read, Update, and Delete) data from the MySQL database. The database is an essential part of WordPress. It is the backbone that stores all core information. The database also ensures that your page runs smoothly, fully optimized, and helps you back-up data regularly.
The next question, where is the database stored?
What is Database Host?
Your WordPress database is arranged by a database host, which then puts it on the MySQL server. Think about the database host as an actual house that you can go to with a specific address (hostname). You can retrieve something from the room section (database name). Of course, you will need permission to that, that’s why you need to enter the username and password before entering the house.
For the most part, you can use localhost to manage and connect WordPress with your database. However, you can also manage MySQL server if your web hosting uses different hostname. In Hostinger, for example, you can access the your MySQL database under the Database section from the control panel.
What is Database Table?
In the database, your data is stored in the form of tables. Each table consists of unique data and is displayed in rows. And the rows also contain other pieces of information or parameters. By default, WordPress will automatically create those tables when you first set up the website. The WordPress 4.4 version will make the following core database tables:
- wp commentmeta – Each comment has unique information called metadata, that data will be available in this section.
- wp comments – All of the comments in the WordPress will be available in this table.
- wp links – This section holds information related to the links entered into the Links feature of WordPress.
- wp options – This section contained data that WordPress uses to store various preferences and configuration settings.
- wp postmeta – Each post has unique information called metadata, that data will be available in this section.
- wp posts – In WordPress, “posts” are articles that you write to populate your blog. This section will store that data. Pages and navigation menu items are also stored here.
- wp termmeta – Each term has unique information called metadata, that data will be available in this section.
- wp terms – The categories for both posts and links and the tags for posts are stored here.
- wp term relationships – Posts are associated with categories and tags from the wp_terms table, and this association is maintained in here.
- wp term taxonomy – This table describes the taxonomy (category, link, or tag) for the entries in the wp_terms table.
- wp usermeta – Each user has unique information called metadata, that data will be available in this section.
- wp users – The list of users is maintained in here.
By default, you can’t remove the WordPress core tables. But it will add to your content. To explore more about the tables, we suggest you read it here.
What is SQL Query?
SQL is short for Structured Query Language; it is a programming language used to manage databases. An SQL request issued to CRUD data in the database server is called a query. WordPress uses MySQL queries to store and retrieve the data and generate it into web pages.
A query can become handy especially if you want to optimize your web page. For example, to remove spam comments that bloat your database and slow down your web page. A query can remove it pretty quickly, run the following command to do that:
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';
You can also perform a query if you want to delete an unwanted post, specifically the older ones. To do that, run the following query:
DELETE * FROM `wp_posts`
WHERE `post_type` = 'post'
AND DATEDIFF(NOW(), `post_date`) > 30
You can change the number “30” to any post dates you wish to remove. In this example, it will remove post older than 30 days.
You can always manually remove spam and older post from the WordPress interface, but the query makes the power literally at your fingertips.
Pro Tip: Remember to do a backup before executing any changes in your database!
How to Manage WordPress Database?
We covered a lot of information regarding database management. Now, let’s put it in good use.
Manage your WordPress database, or rather MySQL server, with phpMyAdmin. It is an open-source web-based application with an easy-to-use user interface. Hostinger has phpMyAdmin in your control panel. You can access it under the Database section:
Or better yet, we’ve already created a unique direct link for you to access phpMyAdmin. Just remember to fill the username and password of your MySQL server. You can find it here:
After you click either the direct link or phpMyAdmin button, it will direct you to phpMyAdmin interface:
As you can see, those are the core database created by WordPress. I haven’t made significant changes in my web page, so there are no additional tables created. You have to remember though, every time you make changes in WordPress, those tables can add and pile up. That’s why you should manage it well.
Let’s perform basic management by creating a backup. Click the Export tab and choose the appropriate option.
That instruction will download the SQL files to your computer.
You can also use plugins to manage your WordPress database. See a list of the best WordPress database management plugins here.
Databases and WordPress are like two sides of the same coin; they work side by side to ensure that your web page works as good as it can, and all of its data well-managed.
Let’s wrap up. We have covered the benefits of having a database to store and retrieve data. We also learn how to use the query to ease our work in managing database, and lastly, we know how to perform basic WordPress database management with phpMyAdmin.