How does WordPress Store Custom Fields? #2 Database

Now, you know what custom fields is and in the previous post, we stopped in the step “WordPress calls the update_metadata function to store data from custom fields in the database”. In this post, we’ll follow up that flow to figure out how WordPress organizes the database.

Regarding the database (MySQL specifically), you may know that the database is organized into tables. There are columns and rows in each table. A row is called a record. And, the columns in the row are called fields. In order to save an object in the database, the simple and usual method is creating a table with a sufficiency of the columns corresponding with the attributes of that object.

Linking it to WordPress with a typical example of Post. A post has some attributes such as title, content, thumbnail, and custom fields. Whether or not WordPress stores these ones in the columns of the wp_posts table? Let’s find out!

Last time, we groped to every code of WordPress to realize its data flow in storing custom fields’ data. But now, we'll see it from the perspective of the database. No code anymore.

Database structure

The wp_posts the table has plenty of columns to describe the post object. It’s similar to Term, Comment, or User. Take a look at the table, it has only necessary columns as the base and background information for most applications such as post_title, post_content. Out of that, which column saves the value of custom fields?

There are none of the columns do that. WordPress doesn’t follow the normal way as I early mention. Instead, WordPress turns the table that has horizontal columns into vertical ones, so each custom field becomes one row. At present, each row is not describing any object. They describe a specific attribute.

Example:

Supposing we have table organized by the following normal way:

And this is how WordPress stores the data in reality:

As you see, we have two rows from the first, and each row has two columns containing two custom fields. But it comes up with 4 fields after converting. You can imagine that we moved entirely the hcf_author column to under the hcf_price one then added columns for defining key and value. Thus, if you have N custom fields and M posts, the number of rows that WordPress creates is M*N.

Obviously, this number is significant, especially when you use the plugin exploiting custom fields strongly such as WooCommerce, Sensie, or others using custom fields to describe a complicated object.

Strength and weakness

Although WordPress is a reputable CMS, why does it still use the above fairly ineffective method to store data from custom fields? It might have its own reasons for this. So let’s find out its strengths and weaknesses.

Strengths

The high ability of customization

According to the common table structure, once you add a custom field, you have to run a migration script to add that column to all rows in the table. Then, the structure of the table will be changed.

Otherwise, with the meta table, no matter how many custom fields which you want to add and what their name is, the structure will be kept intact. There is only one row will be added to the table.

Polymorphism

Pursuant to the common database structure, all objects described in a row will have the same structure and the same columns. If you want to describe another one, you must create another table. However, WordPress supports custom post type which helps us describe many different objects together in the same table (wp_posts, wp_postmeta). It’s possible only when WordPress uses meta tables. It works with objects that have unspecific & different structures.

Because of these two strengths, the database structure of WordPress will not change however much custom fields you add. The structure of table wp_postmeta will be stable whichever objects it describes. That is the reason why it’s necessary to add one step of “migrate database” as the websites which organize database by the common method. Hence, it brings WordPress not only the high ability of customization but also the simple for users.

Weaknesses

Query performance

As noted earlier, to calculate a number of rows, we have the following formula:

The number of rows = The number of posts * The number of custom fields

Supposing that we have 20 custom fields, and 500 posts, there will be 500 * 20 = 10,000 rows in the database. This number is neither significant nor light. Your blog comes with only 500 posts, isn’t it? Try thinking about one year later when the number of posts increases twice or three times.

Nevertheless, how to query 20 custom fields at the same time? Oh, you need 20 JOIN commands, of course. Because each custom field is on one row, you need to join that row if you want to take the custom field.

SELECT * from wp_posts
INNER JOIN wp_postmeta m1 on m1.post_id = wp_posts.ID AND m1.meta_key = "hcf_price"
INNER JOIN wp_postmeta m2 on m2.post_id = wp_posts.ID AND m2.meta_key = "hcf_author"
Where wp_posts.ID = 1

WordPress indexed column meta_key, hence the performance isn’t too low until you reach a large number of rows (hundreds or thousands of rows). Most websites utilizing many custom fields will use the filter or search by the value of the custom fields function also. Then, the query will be:

SELECT * from wp_posts
INNER JOIN wp_postmeta m1 on m1.post_id = wp_posts.ID AND m1.meta_key = "hcf_price"
INNER JOIN wp_postmeta m2 on m2.post_id = wp_posts.ID AND m2.meta_key = "hcf_author"
WHERE m1.meta_value < 100 and m2.author = "metabox"

At this time, a serious problem occurs as WordPress doesn’t index the meta_value. It means that queries based on the meta_value will be very slow. Even so, you can't index the meta_value because they are repeatable, and you also cannot determine their structure.

In short, custom fields are not made for queries.

Trash in the database

Every plugin can add unlimited custom fields to your database. The database from custom fields won't be deleted even when you uninstall the plugin. Someday, when you look back at the database, you will not know whether the custom fields will have been being exploited or not.

Solutions

We can't leave WordPress just because of its weaknesses in the meta table. Moreover, we can’t deny that its strengths are extremely obvious and beneficial. Its problem is querying the database only. Therefore, we will work on things to tackle right this issue.

Put the table horizontally

If the vertical table makes us join a lot and not be able to index, let’s try transposing it horizontally. Each custom field is put on a separate column with a distinct type and ability of index. After that, deep hooking into the allowed hooks to modify the query sentence.

Actually, this method is virtually ineffective. You might have a huge number of tables and update its structure whenever a new custom field is added. It also forces all objects to have the same structure. As a result, the custom fields of different objects (such as Product, Post, Hotel) merge into an enormous messy mass.

Cut the meta table out into small tables

Instead of dealing with queries, we will subdivide tables. You can gather all the custom fields which usually go together, then put them all into a table. The joining will be more effective. You also can limit the maximum number of rows in each table. When that number reaches your limit or satisfies a condition, a new table will be created. At that time, you’ll have lots of meta table. For instance: wp_postmeta_1, wp_postmeta_2, wp_postmeta_3, …

When carrying out a query, you can hook into a filter and create a MySQL query to modify the MySQL command. You also can base on the name of custom fields for identifying the table’s name which you query.

Despite that those are two ways to intervene directly in the database, it is difficult to put them in practice and implies too much risk. Also, it doesn’t go with really significant effects.

Cache

Cache is an effective method in this case and brings great and immediate effect. There are various strategies for cache, generally speaking, they help us restrict querying in the database, cache the query, or even cache the whole website in the static form. There is no need to care about whether the query order is quick or slow. In case it is too slow, it is going to influence a small number of users.

However, in several bad cases once there are a lot of kinds of users’ requests, up-to-date data, or a short life cycle of cache, the quantity of queries into the database is still massive. Don’t worry, we have another solution for you as below.

Use third-party solutions

In general, it means that you have a third party who stands between you and users’ requests to handle requests. You only play the role of a data source. Thus, you won’t worry about the bulky database structure anymore. It now puts on a new structure and is easy to query.

There are several other solutions you can use such as ElasticSearch, Redis, or Algolia.com. You can index your data by the new structure which supports to query at the speed of light.

Conclusion

So far we’ve learned how WordPress builds its database. Thanks to the meta tables, WordPress can expand flexibly and support many types of objects. And it comes with the performance issue. However, because of its advantages, we accept its disadvantages and have to find out ways to solve them all. Some ways like converting from a vertical table to a horizontal one, subdivision or cache the table as the simplest and most effective ways.