WordPress Database Management (2021 Guide)

You might have wondered many times what is WordPress database and how to manage it? This guide explains WordPress database management in detail, including, why you should bother about database management, and how to access the WordPress database. I will then list the exact steps to optimize your WordPress database.

But first…

What is WordPress database?

The database stores all the information about your WordPress site. Think of it as a filing drawer containing all the important files. This database is created using MySQL, an open-source software that is at the heart of WordPress.

However, WordPress does not directly interact with MySQL. Rather the interfacing is brought about by PHP, an open-source scripting language.

This interaction between PHP and MySQL allows you to create dynamic content for your WordPress website.

Why should you bother about your WordPress database?

Your WordPress database may not seem of immediate value to you but, remember, your WordPress database contains your login details, information about your posts and pages, your site comments, your plugin data, and much more.

Once you have created your WordPress website, securing this vital information is crucial for your site’s security apart from the fact that you can optimize your database for reducing its size and speeding up your site.

I will show you the exact steps to achieve all this and more in this guide so let’s get started.

How to access the WordPress database

You can access your WordPress database from your site’s cPanel or control panel. If you are using a managed WordPress hosting provider like I am then you can access it from your hosting provider’s control panel.

You should click on phpMyAdmin within your cPanel dashboard. phpMyAdmin is a software that provides a graphical interface to your MySQL database.

This will open the phpMyAdmin interface. Now click on the Databases tab to access your database(s).

Understanding the WordPress database tables

Your WordPress database consists of different tables that store information in multiple rows.

By default, WordPress contains 12 tables; but your database will contain more than the default number of tables that are created by WordPress to store the data of various plugins installed on your site.

You might be wondering what is the wp_ prefix before the table name?

The wp_ prefix is affixed by default when you install WordPress for the first time. It denotes a WordPress database table.

However, you can and should change your database table prefix to deter hackers from accessing your WordPress database. I’ve explained the steps to change your database table prefix in the WordPress security guide.

Now let’s understand each of the default WordPress database tables in detail.

wp_term meta: The terms meta stores meta-information about WordPress terms like categories, tags etc.

wp_terms: This table stores information related to WordPress terms like category and tag names, and their slug or URL.

wp_term_taxonomy: This table lists the different taxonomies in use on your WordPress site like categories and tags along with their description if you have added it in the post on which those tags and categories appear.

wp_term_relationships: This table lists the relationships between the tags and categories and the taxonomy table. That is it mentions the id of the tags and categories alongside the corresponding id in the taxonomy table.

wp_comment meta: This table contains meta-information about your site comments. Each comment is assigned a unique id and each row is also assigned a unique id. The meta value contains details of the post id on which the comment appears apart from other information required by WordPress.

wp_comments: This table contains the comments made by users on your site along with other information, including, the comment author name, email, and URL if any. It also lists the post id where the comment is posted.

wp_links: The links table contains the data on links you might create using your dashboard widgets. If you have not created any links, this table will be empty.

wp_user meta: This table contains the meta-information about the users on your WordPress site. If you are running a single author blog, it will contain meta-information about your profile like your username, nickname, first name, last name, your profile description among other information.

wp_users: As the name suggests, this table contains information about your username, including, your user login, user nickname, your password (which is encrypted), and user email. All of which is very important stuff.

wp_post meta: This table contains meta-information about your posts and pages, including, the post and page id.

wp_posts: This is a very important table as it contains all your posts and pages data. It also contains other useful information like post title, post status (published or draft), and comment status (whether open or closed).

wp_options: This table contains general information related to your blog like the blog title, description, admin email, mail server, date format etc.

Now that you are aware of all the default WordPress tables, it is time to know about.

WordPress Database Table Tricks

There are many things you can do with the WordPress tables, however before you edit any of the tables, you should first backup your WordPress database.

ad

You can use the UpdraftPlus plugin to backup your WordPress tables. Alternatively, you can also use the phpMyAdmin interface to generate a database backup.

Just click on the Export tab on the top. Then under the Export method options, select the custom checkbox. Make sure all the tables are selected and select the zipped option under compression method.

Once you have backed up your WordPress database, it is time for some cool tricks.

1. You can see the number of tables and the size of your WordPress database from within phpMyAdmin.
2. There are two very useful actions you can perform within your database dashboard: Empty table and Drop table.
3. If you click the Empty table link, it will clear the table of its data without deleting the table entirely. But if you click on the Drop table link, it will delete the complete table along with its data. So you should use these options with utmost caution.
4. You can also change your username and user login easily.
5. You can also import complete tables into your database by using the Import option.
6. You can also search for tables of specific plugins by using the Filters box under the structure tab in the dashboard. For instance, the Yoast SEO plugin’s tables start with wp_yoast. So you just need to enter yoast in the Filters box and all the Yoast SEO plugin tables will be displayed.
7. Your WordPress tables are also sortable. So if you wish to identify your biggest tables by size, you should click on the Size tab. And it will sort your tables in descending order of their size. Many of these tables have been created by different plugins. So you now know exactly how much each plugin is contributing to the size of your WordPress database.

How you can use this information to optimize your site?

All this talk about WordPress database is of no value if you cannot put it to practice; so let’s use our newly gained knowledge at once.

1. Once you know the list of plugins by their database size, you can remove redundant plugins to reduce your database size. This can also result in improvement in your site loading times.
2. Many plugins that are inactive or have been deleted still retain their data in the database. You should delete the inactive plugins and delete such tables that do not serve any purpose but only contribute to the database bloat.
3. So let’s say that I am no longer using the Yoast SEO plugin, then I can simply drop its tables from the database. But before you do this, you should first delete the plugin from your admin dashboard, then back up your database and only then proceed to delete or drop the tables.
4. If your comments database size is very large, make sure that your spam comments are being deleted at regular intervals. Also, if your site receives a large number of comments, then you can consider using a non-native comments solution like Disqus to keep your WordPress database free of comment bloat.
5. You can also optimize your WordPress tables by selecting all the tables and then clicking on Optimize table under the Table Maintenance sub-heading of the dropdown. This will restore the scattered files to their original location and your site should experience a small speed boost.

Using a plugin to optimize the WordPress database

You can also use the WP-Optimize plugin to optimize your WordPress database. This wonderful plugin has many features but its main tasks are database and image optimization, and site caching.

But for this topic, we will just focus on its database optimization features.

One of the cool features of this plugin is that allows you to backup your database using Updraftplus. To use this feature, you should have Updraftplus installed.

Click on the Database option under the WP-Optimize dashboard menu on the left.

WP-Optimize allows you to perform various optimizations on your database like:

1. One-click database table optimization
2. Cleaning up the post revisions that are saved by WordPress whenever you write a post
3. Cleaning up spam and trashed comments
4. Cleaning up post metadata
5. Removing pingbacks and trackbacks
6. and much more

The other really useful feature of this plugin is removing database tables that are inactive or not in use. To use this feature, click on the Tables tab.

This tab displays all your WordPress tables. The tables are sortable by different parameters like the number of records, data size, index size and so on. We are concerned with tables that are inactive (created by inactive plugins) or are redundant since the plugins that created these tables are no longer installed on your site.

You can click on the Remove button to delete these tables and reduce your database bloat. But as I have reminded you earlier, you should always back up your database before you run the various optimization features of this plugin.

Using WP-Optimize plugin, I managed to shrink the database size of my niche site from 220 MB to 145 MB. That is a decrease of 34 percent!.

This was WordPress database management in a nutshell. Use this information to optimize your WordPress site. Also, give me a shout in the comments section below if you successfully optimized your WordPress database using these tips.