Optimizing Database for Custom Fields in WordPress

Custom fields is a way for WordPress to store arbitrary extra data for content (posts and custom post types), such as author name, published date for a book. To make custom fields flexible and compatible with different kinds of data, WordPress designs the meta tables (post meta, term meta, user meta, and comment meta) in the form of key-value. According to that, each custom field is stored as one row in the database. This approach allows developers to store unlimited data regardless of its structure. But, the downside is the rapid bloat of the database. Because the number of custom fields is usually very large. This article will present solutions to optimize the storage of custom fields in the database to help boost your website performance.

Common problems with WordPress database structure for custom fields

There are some disadvantages of using the WordPress default database structure for meta tables as follows:

Unclear, fragmentary data model

Because stored as a key value, the data of the same post is not stored in the same row in the database. The lines are not consecutive and unordered. Therefore the data is discrete and does not represent the data model of the post. See the picture below:

While we want the data to be described more clearly as follows:

The clearer data model in the database helps us manage the data and know what data a post needs. Since it will affect how we organize the code to get/set the data more effectively.

Bad performance

With the current table structure where one custom field is stored in one row in the database, we’ll face with the bloat of the database very soon. If there is a way to save all custom fields into a single row in the database, the number of rows in the database will decrease significantly, resulting in reduced database size and increased the performance when accessing the database. A case from Easy Digital Downloads proves that optimize tables for custom fields can increase the performance up to 3 times.

Besides, saving each of the custom fields on a single row will limit the ability to query the data by custom fields. Although this is not recommended, we still have to query by custom fields in practice. Meanwhile, the structure of the database will prevent the creation of complex queries.

So to solve the database problem for custom fields, we need to change the way we store custom fields.

How to optimize storage for custom fields

As noted, custom fields are not recommended for searching or query, although in practice, sometimes we have to perform such operations. Thus, the optimization of storage for custom fields should be divided into two problems: the optimization of storage for custom fields used for displaying/saving purposes only and optimizing for custom fields related to the data query.

Optimizing storage for custom fields used for displaying/saving purposes only

Since custom fields are stored in different rows in the database, the simplest way to solve this problem is to save them in a single row in the database. How to do it? It’s very simple: save all the information to an array and save the array to a row in the database.

// Add to database
$details = array(
    'address' => '746 Sugarfoot Lane, Richmond, IN 47374',
    'area' => 150,
    'price' => 250000,
    'type' => 'Land',
);
add_post_meta( $post_id, 'details', $details );

// Get
$details = get_post_meta( $post_id, 'details', true );
echo $details['address'];

Note that WordPress does not care about the type or structure of the data you save in a custom field. You can save anything to your custom field. If it’s an array or object, it will be serialized before writing to the database. When retrieved, it will be unserialized and you will receive the correct data format. This is an important point that gives us the flexibility to save data with custom fields.

Because our data does not need to be queried, so even if stored in the serialized form, it does not matter.

With Meta Box Group, all you need to do is declaring all fields as sub-fields of a group:

// Register meta box and fields
add_filter( 'rwmb_meta_boxes', function( $meta_boxes ) {
    $fields = array(
        array(
            'id' => 'address',
            'name' => 'Address',
            'type' => 'text',
        ),
        array(
            'id' => 'area',
            'name' => 'Area',
            'type' => 'number',
        ),
        array(
            'id' => 'price',
            'name' => 'Price',
            'type' => 'number',
        ),
    );
    $meta_boxes[] = array(
        'title' => 'Property Details',
        'fields' => array(
            array(
                'id' => 'details',
                'type' => 'group',
                'fields' => $fields,
            ),
        ),
    );
    return $meta_boxes;
} );

// Get
$details = get_post_meta( $post_id, 'details', true );
echo $details['address'];

Pros:

• All data is saved in a single row in the database.
• Take advantage of the Metadata API for retrieving and updating data.

Cons:

• The data structure stored in the database is still unclear.

Optimizing for custom fields related to query

Saving all custom fields to a serialized array helps us solve the storage problem. But, if custom fields are used for queries, the problem becomes more complicated and we need other solutions.

The easiest solution is to keep the same serialized data and use a plugin to query the data. You can check to the SearchWP plugin, it does the searching job quite well.

Besides, you can use a trick to query data by custom fields: save custom fields that need to be queried separately and save the remaining custom fields to a serialized array. For example, with a real estate site, you can query price by saving it to a separate field, as follows:

// Price
add_post_meta( $post_id, 'price', 250000 );

// Other fields
$details = array(
    'address' => '746 Sugarfoot Lane, Richmond, IN 47374',
    'area' => 150,
    'type' => 'Land',
);
add_post_meta( $post_id, 'details', $details );

If you’re using Meta Box Group, you can hook to store price as follows:

add_action( 'save_post', function ($post_id) {
    $details = get_post_meta( $post_id, 'details', true );
    if ( isset( $details['price'] ) ) {
        update_post_meta( $post_id, 'price', $details['price'] );
    }
}, 20 );

Although this solves the query problem for simple custom fields, it will not work well if you need to retrieve data from many custom fields. Because you will have to save them separately, which means many rows in the database. On the other hand, it does not solve the problem of making the data structure clearer or increasing query performance.

Pros:

• Take advantage of Metadata API to retrieve and update data.
• Optimize the number of lines stored in the database at a moderate level.
• Ability to query data at a moderate level.

Cons:

• When the number of custom fields to query is large, the number of rows in the database increases.
• The database structure is still unclear.

Using custom tables

To solve this problem completely, we need to save data in a different way than WordPress: using custom tables. With custom tables, you can specify the number, the type of data to be saved, and how to save it. For example, we can design the database as follows:

Then the data structure will be very clear when viewing the database as follows:

To do this, we have to do everything ourselves from designing the database, writing data get/set functions, and even designing cache layer when data is growing. This can be a big problem for most WordPress developers as they are already familiar with the use of built-in functions for data processing.

Although you have to do all the work yourself, the benefits are great when you work with a lot of data: better scalability, clearer data, manageability and you can handle, query data as you like.

Pros:

• Clear data structure.
• Can query by custom fields and create complex queries.
• All custom fields are saved on the same line.

Cons:

• Not using the built-in WordPress metadata API functions.
• Self-assurance of data security.
• You have to implement cache layer when the data is growing by yourself.

Conclusion

As the data is growing, performance and scalability becomes big problems for all websites. We have to think about optimizing the storage for custom fields in the database. With some techniques in the article, you can save the storage space of the database and at the same time increase the performance of querying the data.