WordPress Database Essentials and Optimization

A database is a critical component of the website, and understanding the essentials about what it is and how it works in WordPress is really helpful if you want to create optimized and well-structured websites, understand the principles of data querying and even get the idea which themes and plugins will do the best job for each particular project. 

WordPress database also needs optimization from time to time. And in this article, I will dive into this topic, too. 

Table of Contents

• What Is a WordPress Database?
• Why Optimize a WordPress Database?
• What Clogs a WordPress Database, and How to Delete It?
  • Revisions
  • Spam and trashed comments
  • Trashed posts and drafts
  • Transients
  • Orphaned records
  • Database optimization and repairing
• To Wrap It Up

What Is a WordPress Database?

Let’s take a look at a very simplified concept of any WordPress website. It consists of just two main components:

1. Physical files (PHP, CSS, JS) that define the configuration, appearance, and rendering. You can see and edit them using an FTP client.
2. A database (DB) that stores content, users’ information, relations, and other data. You can open and edit it using various tools, and one of the most popular is phpMyAdmin.

Every time a user opens a website, a connection to a database is established, and requests are sent. Then, a browser processes the content, and the user can see the requested webpage. So, no WordPress website can exist without a database. It uses an open-source DB management system called MySQL. 

Before making a website, a database must be created. Many hosting providers, especially managed hostings, do it automatically when you hit the “Install WordPress” button on the hosting panel. The name of the database, login, and password can be found in the wp_config.php file; without them, there will be no connection to the DB. 

There are 12 default tables in the WordPress database to form the main structure, and more can be added when specific plugins are installed. This is the list of the core database tables:

• wp_posts – the central table that stores all the post types (posts, pages, menus, revisions, etc.). So, this table is responsible for most of the website’s content.
• wp_users – for information about registered users (logins, passwords, nicknames, etc.).
• wp_comments – for comments.
• wp_postmeta – holds posts’ metadata (data from meta fields).
• wp_usermeta – holds metadata about users.
• wp_commentmeta – holds metadata about comments.
• wp_terms – stores taxonomy terms (names of categories, tags and custom taxonomies, their IDs and slugs).
• wp_termmeta – stores terms’ metadata.
• wp_term_taxonomy – describes the taxonomies and holds information terms’ structure.
• wp_term_relationships – is responsible for connections between posts and terms they belong to.
• wp_options – stores global configuration information, various preferences, widgets, and other global data.
• wp_links – stores information about links from the deprecated Link feature (blogroll). That’s why now, as a rule, it stays empty.

Each table has a certain set of columns (fields) of predefined type (e.g., text, datetime, varchar, etc.) and rows. The number of rows depends on the amount of content the website has. 

Tables in the database are interrelated with the help of the keys (and we will talk about them below). This is a structure of WordPress tables, and, of course, if there are more of them, they will be connected, too:

Useful notes for understanding databases

• The wp_term_relationship table is not for adding some new data. It plays a role of a connector between posts and taxonomies because they have a many-to-many relationship type (when, for example, one post can belong to several categories and a category can have many posts), so this kind of medium is needed to create proper pairs. This is how it works:

• Media files are not stored in the database; they can be found in the wp-content > uploads folder. But records about them and their meta are stored in the DB, mostly in the wp_postmeta table. 
• There are a lot of hidden meta fields that exist in every post. They start from “_” and should not be edited manually. So, if you don’t see meta fields in your posts, it doesn’t mean they don’t exist. Unlike the custom fields you add and want to be edited manually and hold additional information – they, of course, will be visible. 

Database keys

DB tables are connected based on the relationship principle with the help of keys. There are two major types of keys that help to build those connections: primary key and foreign key. (There are more key types that exist, but those two are enough for understanding the idea). A primary key is a unique record in the database that will be used in another table as a reference (foreign key). As a rule, IDs play the role of keys. 

For example, let’s look at the two tables: wp_users and wp_posts. In the first table, we will see that the user John has ID=3. It’s a primary key.

Now, let’s look at the wp_posts table. In the post_author column, we see 3, which points to the user ID=3 (and it means that John is the author of posts with IDs number 9, 10, and 14). This column plays a role of a foreign key, pointing to the primary key in another table. 

Why does the wp_posts refer to the wp_users table, not vice versa?

Because wp_users has a one-to-many relationship with the wp_post table – one user can create many posts, while one post can’t have many authors (users). 

But, in its turn, wp_posts has a one-to-many relationship with the wp_postmeta table. And if we take, let’s say, a post with ID=10 from the first table (primary key) and go to the wp_postmeta table, we will find it in the post ID column (foreign key), and the metadata that belongs to that post. 

Such relation database design helps to dramatically reduce the DB size (because there’s no need to repeat the same information in each table) and helps to maintain data integrity, security, scalability, and, overall, its faster and more efficient work. 

Why Optimize a WordPress Database? 

The most frequent problem that happens to the database is when it becomes huge and bloated, but not because you have added a lot of content. It slows down the website because more time is needed to go through all the DB records and find the requested one. 

What Clogs a WordPress Database, and How to Delete It?

Revisions

WordPress saves post revisions; you can see how many of them each post has on the right panel, open them and restore any of the previous versions.

By default, the number of saved post revisions is not limited. So, just imagine how many extra records the database might have, and they significantly bloat it. That’s why there are two things that should be done to fix this problem: 

• limiting the number of revisions that WordPress saves;
• deleting old revisions. 

To limit the number of revisions, add this line to the wp-config.php file, which can be found in the root folder of the website:

define( 'WP_POST_REVISIONS', 5 );

In my example, “5” is the maximum number of revisions I want to keep. You can put the number that works best for your site. But if you want to disable the revision functionality completely, add this line instead: 

define( 'WP_POST_REVISIONS', false );

The best way to delete revisions is to use an optimization plugin. As a rule, it gives a choice of how many of them should be removed. But also, you can do it directly using phpMyAdmin or similar clients by writing the SQL query (it will delete all revisions):

DELETE FROM wp_posts WHERE post_type = "revision"; 

Spam and trashed comments

To keep the database clean and neat, don’t forget to delete trashed and spam comments. It’s very easy to do in the Comments section of the WordPress dashboard. But also, don’t forget to check the Settings > Discussion section to set up moderation rules and add disallowed keywords. 

Trashed posts and drafts

It’s useful to inspect your post and page lists and check if there are many drafts that you just forgot. 

In addition to them, there are trashed posts that can be permanently deleted manually instead of waiting 30 days until WordPress automatically deletes them. Also, you can add this line to the wp-config.php file to define how often you want your trash to be emptied (in my example, “4” is the number of days posts will stay in the trash and deleted after that):

define('EMPTY_TRASH_DAYS', 4 );

Transients

Transients are temporary data about settings with an expiration date, but it’s not being deleted automatically. So, it’s important to delete them from time to time, especially if you have many plugins. They bloat the wp_options table. And the best way to do it is by using an optimizations plugin. 

Orphaned records

Orphaned records are records that have foreign keys referring to non-existing primary keys. But they definitely litter a database, so they should be deleted. Again, just use a plugin that will do it for you.

Database optimization and repairing 

There are two major methods to make your database works better: optimizing and repairing. It can be done using performance optimization plugins or manually by writing a WP CLI command or an SQL request 

OPTIMIZE TABLE table_name;

It will start the table defragmentation, which is basically a full reorganization and recreation of all the connections anew. 

When’s optimization needed? Well, there can be several reasons for that:

• the database is not working properly, and requests take longer than they should or don’t work at all;
• the size of the database is much bigger than it should be;
• other suspicious behavior of the database (it often happens after migration as well). 

If you experience something like this, back up your website and run a database optimization. 

Actually, WordPress has a specific script for it. But first, you should allow to use it in the wp-config.php file by adding the following line:

const WP_ALLOW_REPAIR = true;

or

define( 'WP_ALLOW_REPAIR', true );

After that, use the following URL – https://your-website/wp-admin/maint/repair.php – and you will see a screen like this:

To Wrap It Up

There are many different reasons why the WordPress database can have a lot of data that should be cleaned up. Except for the obvious (trashed posts, comments, revisions, etc.), there can be many different reasons why the database might be bloated with unnecessary data – depending on the project, its architecture, and used plugins. Also, if the website migrated from hosting to hosting, the risk that the database might not work as expected is even higher. 

Don’t be tricked by the fact that the database normally doesn’t weigh a lot in the sense of what we used to call “a lot” (because it doesn’t have heavy media files). The website sends hundreds of requests to it every time the user opens a webpage. So, it must be well-optimized to retrieve the data in no time. 

The best way to optimize your website database is to use the right plugin. But it’s essential to back it up before doing any actions. Because the process doesn’t go as smoothly as expected in many cases: don’t forget that we deal with many relations, and one wrong setting can break the whole website structure.