Optimizing WordPress – database optimization issues and solutions

In the following article you will find a good piece of information on Optimizing WordPress and database optimization issues followed by solutions. There’s a very good chance that most of the editable content you can see on a WordPress-powered site is stored in the underlying relational SQL database. This includes posts, pages, category lists, shop products, or even your account name and password (of course encrypted as you might guess). The speed of retrieving, inserting, and updating this data depends on the implementation of a data model, the amount of stored information, and the underlying infrastructure. Some of the data may come from external sources, like third-party services accessed via API. However, it’s usually beyond our control of how remote data suppliers process our requests.

Fortunately, we can control data processes that operate on the database that our precious content management system is installed and try to increase database performance. In this article, we are going to focus on this layer. The WordPress database, how it’s used, what are the limits, and how to overcome them, while performing database optimization.

Data in WordPress

The WordPress default database schema consists of a couple of tables that are being used basically in every request to both frontend and backend sites. Among others, there are tables for posts and pages, comments, terms, user accounts, and settings. By default, these tables are sufficient to store all kinds of content – complex pages templates, articles, images information (media library), category lists, editable configuration options, or even temporary data (for example used to buffer responses from external services or delegated to save results of data processing tasks for a custom period of time).

WordPress authors did a very decent job to optimize resource usage and designed tables in a way that they can store an endless amount of data (limited rather by DBMS capabilities and hardware limits) regardless of the data type and its structure. Personal blogs, landing pages, or small shops (with a couple of products) can play smoothly on these universal tables. Issues arise when it comes to processing thousands of entries, especially posts which are the generic containers to any kind of data in WordPress.

Custom entity

What’s important for a database administrator, WordPress gives developers the ability to save any type of custom entity (like shop order, product specification, booking information, and more) as a post, an object with a unique identifier, name, content, date of creation/modification, associated with a particular user. There are other attributes also – but let’s focus on posts in general. What’s more, each of these entities can have several metadata entries attached with scalar and non-scalar data types (arrays and objects can be serialized and stored as texts).

Meta entries act like key-value pairs that are associated with a post. It’s in fact very handy to manipulate entities as posts and add a set of meta parameters to them. There are built-in functions to simplify read and write operations in PHP. The easiest and fastest way to store and retrieve data from the database server is to use this universal post-oriented approach. There are even plugins that bring this ability directly to the WordPress admin dashboard for the convenience of every database administrator. But keep in mind that you will get into database performance troubles at some point as the amount of data collected in the database server grows.

WordPress data types

In terms of data that are processed by WordPress itself or by any plugin (or an extension to an existing plugin) we can distinguish the following types of information:

• domain data – posts and other entities representing any kind of an object with properties (e.g., pages, articles, products, orders, etc.), as well as taxonomies – features that can be attached to any object (e.g., tags, categories, countries, currencies),
• user data – information entered by the end-users, for instance, comments and reactions with relation to domain entities,
• global settings – website configuration options, plugins’ license information, internal settings, and so on,
• user settings – user configuration parameters that one can use to customize the look of the website and behavior of different functions,
• temporary data – volatile information gathered during the processing of requests, usually valuable only for a certain amount of time,
• logs – incremental data collected for analysis or as history records
• others – for example binary data like media files, usually stored as files rather than records in the database.

Settings (global and user) can be easily managed by WordPress as well as temporary data (WordPress provides mechanics called transients that allow to store temporary parameters in the database and define their time-to-live). You can store logs in separate tables, in files, or send them to external services via API. WordPress taxonomies are handled and stored in a very similar manner as posts. They are called terms and can also have meta parameters – usually simple in terms of attributes and rather limited to a fixed number of entries (like categories). However, the domain data is the one that needs special attention, mainly because of the defined requirements, size of single entities, and relations.

WordPress database optimization – scalability issues

As mentioned earlier, you can use WordPress posts to represent any kind of entity. By setting up a website and implementing everything using posts we end up with:

• static content stored in the same place as dynamic content – data that is being fetched frequently is located in the same table that’s constantly growing dynamic data – e.g., page templates (mostly static) are stored along with shop orders (incremental and dynamic),
• obsolete content in the same place as valuable content – trashed posts are stored in the same table as every other entity,
• orphaned metadata left by interrupted processes – some posts might get deleted and left meta entries that no longer point to their parents.

Having everything in one place may also hit the performance of plugins that extend current posts with additional metadata, like the Advanced Custom Fields plugin. Depending on the number of custom fields stored for every post of a given type, there are corresponding meta records that clog up the common database table and impact database performance.

Multisite feature

Depending on a business model, one out-of-box solution to spread data across many places is the multisite feature. You can convert your installation into a multisite environment. In that case, the content will divide into separate spaces – tables for each site will have a unique prefix so posts and their meta entries from one site won’t interfere with others. On the database level, this is achieved by having multiple tables set for each site so data can be distributed across multiple files. This solution still won’t be effective if at least one of the sites collects dynamic data as posts.

The best approach to mitigate the risk of having too much data in a single place is to design a custom tables schema and implement dedicated read/write logic. This should be the first decision to be made before starting any new project with a custom plugin.

When your project is still on paper, ask yourself the following questions:

• Will there be any static content stored in the SQL database and frequently used to handle requests?
• Is there any incremental data expected to be created by administrators and end-users?
• Will there be a requirement to search data inserted by the plugin’s code?
• Are WordPress posts going to be extended with custom properties?

If the answer to any of the above questions is „yes” then you should consider handling your data in custom tables. In the beginning, it may look like overkill, but over time and megabytes of data later it will show its hidden potential. In terms of query performance, there’s also a mandatory step to add indexes but we will get to this later.

Custom database schema – the first step in the WordPress database optimization process

The first step that one should take at the very start of any project is to design the domain data model. Each entity must be defined in terms of data definition and relations with other entities. WordPress’s post-oriented approach allows developers to skip the part of the thinking of data types and focus on business logic. On the other hand, any custom solution (based on dedicated tables and custom model) should include this step of data definition. Clearly defined data types help validate data and ensure proper types of attributes are exchanged between functions or modules. Moreover, wisely chosen data types can improve the query performance of read and write operations in the underlying database.

Database performance

It’s also crucial for database performance and WordPress database optimization to define indexes on certain attributes, based on actual needs for data search criteria. Indexes may speed up queries when they are properly defined and used but may also slow down DML commands (Data Manipulation Language) if there are too many of them. Indexes can be added at any later phase, but for starters, they should be defined for all attributes carrying information about record identifiers and relations between tables (primary and foreign keys). A good choice is to use composite indexes depending on the expected query scope, as WordPress does in its tables, so the overhead of storing and managing indexes can be lowered.

You can save data, in the MySQL (or MariaDB) database, in tables that support row-level blocking and transactions or in tables oriented on rather transaction-less operations. The good choice for the former is the InnoDB database engine and for the latter – is MyISAM (or Aria for MariaDB). Nevertheless, I recommend to base all of the custom tables on the InnoDB engine, except for tables used for logging which you can safely store in tables powered by MyISAM (incremental data – more inserts than look-ups, rollbacks not expected).

Data operations

Having the custom schema designed, we can move on to the data operations handling in PHP. WordPress gives developers a great class, wpdb, that you can use to perform all kinds of SQL queries and commands on the underlying database, including a selection of proper table name prefixes, statements preparations, and data fetching by rows or columns. It’s worth mentioning that in case of reducing wpdb overhead you can directly use PDO or mysqli PHP extensions and their functions to query the database. Just keep in mind that you will have to handle different operations on your own, for example by implementing a custom wrapper class. No matter what approach you chose, in order of preventing SQL database injection attacks, remember to use prepared statements and bind custom parameters instead of injecting them as strings in SQL code.

dbDelta

There’s also a very handy tool designed to keep your custom table definitions up to date on the PHP level. It’s called dbDelta and is available in WordPress as a function with the same name. You can add or change columns in existing database tables along with plugin updates automatically based on existing and new table definitions. dbDelta takes care of extracting changes between schemas and applying DDL (Data Definition Language) commands as necessary.

As you can see, any custom work around a database-oriented project requires at least minimal knowledge of writing SQL queries and commands – from table definitions (CREATE TABLE) to search queries (SELECT). WordPress’s post-oriented approach allows for the implementation of any kind of data manipulation and searches logic purely in PHP as all SQL code is being generated under the hood. But like anything that comes easily, this also comes with limitations.
Lastly, depending on the purpose of historical data stored in custom tables there may be a need to clean up obsolete records periodically. This requires custom SQL commands to be implemented. But again, running them should be much faster than scanning and cleaning WordPress posts and meta tables. You can register any custom code as a WordPress cron job and then trigger it, for example, once a week or month.

Solutions on the market

Usage of custom tables isn’t anything new nor uncommon. Let’s take a look at some of the most popular applications and products as they present good examples of possible solutions to everyday performance issues.

The most popular e-commerce plugin, WooCommerce, uses custom tables to some extent. It stores some of its data in separate tables in the WordPress database to speed up queries. At the time of writing this article, Automattic is about to introduce custom tables for orders soon.

This should solve the problem with WordPress database optimization i.e. posts and meta tables overloaded with order data.

Advanced Custom Fields

For the Advanced Custom Fields plugin, there’s a companion plugin called ACF Custom Database Tables that allows to store ACF groups and fields in separate tables in the database. Each ACF group can contain lots of different fields of any type. By default, ACF stores these fields as meta entries next to posts which may lead to overloaded WordPress posts meta table. The aforementioned plugin enables the saving of these custom fields in separate tables in the database (for each group). Each field has its column rather than being just an independent meta record.

The recent version of the plugin allows to configure attribute types for any field. By default every field is stored as a text which is convenient but doesn’t allow to force data types upon saving. Having domain data spread across many tables that expand horizontally along with adding new fields to every entity helps to manage a strictly defined data model and improves search queries. This also has its flaws, but if you see the downsides of this approach then probably WordPress isn’t a good platform to base your project on anyway.

Many plugins responsible for logging activities – for instance: requests to external APIs, and changes to WordPress entities – also use custom tables to store incremental information about executed commands and results. As you may guess, this also improves search operations and helps to keep all non-domain data separately.

WordPress database optimization – DBMS tuning

Optimizing the way your code stores and retrieves data is one thing. No less important is the configuration of the software that powers the WordPress instance, namely DBMS (Database Management System). As mentioned earlier, WordPress takes advantage of MySQL (or MariaDB) which is a quite efficient relational database software. There are areas where relational database performance is worse than the object-oriented ones, but it’s out of the scope of this article. Like every service, MySQL requires tuning depending on actual purpose and load. There is plenty of tutorials explaining how to select proper values for parameters responsible for performance gain.

Definitely, it’s worth to configure and tailor them to real needs and hardware capabilities. One of the most important ones is the InnoDB buffer size that defines the amount of memory MySQL can allocate for frequently read data from InnoDB tables. Having data in RAM improves performance as referring to mass storage devices is usually slower.

When it comes to serious database optimization for data processing in terms of a huge number of records or high traffic, then I always recommend to have DBMS running on an infrastructure separated from the web server that processes HTTP requests. WordPress allows to configure the address and port of the underlying database connection, so the configuration doesn’t require anything special on its side.

Data maintenance in the WordPress database optimization process

Every piece of information stored in the database presents a value at some point. Domain data powers business processes, logs support debugging, and settings allow to customize and personalize website look or behavior. As mentioned earlier, over time, trashed or orphaned data may get collected, and in most cases, this obsolete data just occupy space without being even read by any relevant processes.

Any data model implemented as custom tables in the database should be designed in a way that makes the identification of obsolete data-efficient and quick. Operations that make use of the DELETE command in SQL can utilize indexes to speed up data search, so running them on specialized custom tables will definitely take less time to execute than searching through posts. Moreover, in some cases, you can clear out entire tables of records can amazingly fast using the TRUNCATE command. All of this depends on the particular use case, but in general, there shouldn’t be an issue with identifying entities and their related entries designated for removal.

Database administrator can trigger periodical data clean-ups, on-demand or automatically using cron jobs, for keeping database performance . There are plugins in the WordPress official repository that help to remove trashed posts, orphaned meta entries, transient settings, or outdated entities with one click. However, any data stored in a custom place needs to be taken care of by a custom logic that performs removals based on a unique schema.

Summary – how to achieve good WordPress database optimization?

Every WordPress-powered site uses its underlying database to some extent to store and serve content. WordPress supplies developers with very intuitive mechanics of posts but at the same time opens a gateway to database performance issues. Small sites, focused mainly on static content can play quite efficiently on this built-in solution, but bigger and more complex services need a smarter approach. 

A custom data model can make use of custom database tables – a dedicated storage place for well-known entities, meta entries, etc. Relational database management systems, like MySQL (and its increasingly popular fork, MariaDB), are designed to handle such strictly defined data models in terms of forced data types and structure. You can execute batches of SQL queries and commands in the scope of single transactions which provides a better level of isolation and speed up processes of data modification. It’s worth mentioning that WordPress doesn’t use transactions for its internal purposes but that doesn’t mean you can’t rely on them in your custom plugins

There are solutions on the market that enable custom tables for existing WordPress posts. For instance, the extension to the ACF plugin described earlier. It’s focused on providing a convenient graphical interface as well as it’s an automatic replacement of operations that route data to particular tables. Other products also go this way – it’s wise to keep data away from places that are designed for everything but at the same time burdened with low efficiency.

Conclusion – WordPress database optimization

To conclude, any complex system that processes data more complex than standard WordPress posts should be based on a custom data model. It should be powered by dedicated database tables, and implement a separate layer of data access and manipulation. It comes with a cost, but it’s worth it.

What’s more – with sufficient time spent on designing proper architecture, WordPress is a great tool also for projects requiring high scalability both in terms of high traffic and amounts of data stored in the database. It just requires high engineering skills, as at a certain scale we cannot count that database performance will be provided “out of the box”. Still, investing in the proper tuning and optimization of the WordPress installation can be worth the effort. We can take advantage of all its flexibility coming from the huge ecosystem of ready-made solutions available for this platform.