Large scale WordPress sites may at times suffer from performance issues, particularly with heavy use of meta data within posts and users. This article focuses on meta data and offers possible workarounds to get the most out of your WordPress install.
If you’re a developer like me, you may (or may not) have experienced performance issues developing and deploying sites using WordPress. There are a number of reasons performance hits may occur, some including:
- Poorly written code
- Web host issues including infrastructure bottlenecks and the likes
- Poor database performance
- Poorly optimised database queries
- Too many database queries
- Too many plugins loaded
These are just a few of the potential performance issues you may run into. This article deals specifically with meta data related performance issues and possible ways to address the problems. There are many resources available online to address other performance related issues. A simple Google search will reveal these.
Background of Meta Data Storage in WordPress
The WordPress data model for storing meta data (both for user-meta and post-meta) is an implementation of EAV (Entity-Attribute-Value).
Wikipedia describes this arcitecture:
“This data representation is analogous to space-efficient methods of storing a sparse matrix, where only non-empty values are stored. In an EAV data model, each attribute-value pair is a fact describing an entity, and a row in an EAV table stores a single fact. EAV tables are often described as “long and skinny”: “long” refers to the number of rows, “skinny” to the few columns.”
EAV allows for unlimited storage of any data about a particular entity (that is, a post, or a user) in a vertical scape. In fact not only does it allow for unlimited attributes, you may also store multiple values of the same attribute which are stored in multiple rows.
In most cases, in particular for simple WordPress installs, this model has server the framework well and continues to do so.
Problems with Meta Data Storage in WordPress
WordPress is an extremely popular content management system and framework for deploying virtually any type of website from a simple blog to a highly customised e-commerce solution, and as popularity increases, larger websites have been migrating to the framework.
With any large website often there is an array of additional data storage required to handle the custom functionality within the site. Within WordPress, using the EAV model this can sometimes lead performance issues.
Case Study – Membership Site
Take for example a membership website (a development I am currently working on) that has 1,000 memberships (which by no means is a large number). The membership site will store the members profile including Address, City, State, Post Code, Country, Interests, site preferences etc.. along with a number of other ‘meta’ storage fields.
If we assume that each user will have an additional 15 additional ‘meta’ storage fields the amount of rows in the meta table needed to store this data will equate to 15,000 rows! Again, this is by no means a large number, and with the EAV model MySQL will happily handle this size without any significant performance issues. But let’s begin to increase the size of our membership site up to 10,000 users. Along with 10,000 users will come an additional 150,000 rows in the meta table required to store our 15 bits of ‘meta’ data. Now these numbers are starting to look a little bigger! In additional to the 150,000 rows required for membership data, there will most likely be additional data that will be stored by other plugins and the WordPress core itself which will increase this figure even more.
WordPress should still generally be ok in querying single key values from its meta tables. This is due to the fact that the entity (post/user id) and attribute (meta key) are both indexed in MySql.
However lets assume that you need to query a particular value stored in meta, returning posts that only match a particular meta data value. This is where you may start to run into performance issues, especially if you are wanting to query multiple values in the one query, for example processing data from an advanced search form. This can significantly degrade the performance of your WordPress site.
Possible Workarounds to Increase Performance
There are a number of possible solutions that may help address performance based issues with meta data. These are solutions I have used in the past and still use now.
1. Index the meta_value column.
Well, a partial index. By default, the WordPress database schema does not contain an index for the meta_value column and probably rightly so as the column is a LONG TEXT data type. MySql does not allow a LONG TEXT field to be fully indexed due to the fact that a LONG TEXT (as far as I’m aware) is stored separately from the rest of the data.
MySql will allow you to create a partial index on the meta_value which that in itself can sometimes be enough to solve your performance issues. However, it’s not the best solution, as WordPress queries still need to ‘cast’ data into specific data types to perform queries. For large data stores, this is a slow process, especially for a LONGTEXT field.
You can create a partial index using the following code:
ALTER TABLE `wp_usermeta` ADD INDEX USING BTREE (meta_value(255));
ALTER TABLE `wp_postmeta` ADD INDEX USING BTREE (meta_value(255));
2. Re-asses the type of data that is stored in your meta table.
The EAV data model is not efficient enough to handle queries from the value column when accessing large data sets. But perhaps the type of data being stored in WordPress’ meta table should not have been there in the first place.
There are certain types of data that should be stored in custom taxonomies. Examples of this type of data might include: Countries, Categories, Membership Types, Statuses in addition to others. These are generally data types that appear in lists.
This solution often needs to be thought of prior to your development. Approaching this solution after your project has gone into production can be tricky and can lead to some serious code rewrites.
This is a more elegant solution than the previous, however there a few draw backs. The first obvious drawback is a lack of support for user based taxonomies. This can be solved through the use of an additional plugin (User Taxonomies). I have not used this plugin myself however I would assume it would be implemented in a manner identical or at least similar to Post taxonomies.
3. Changing the meta data storage mechanism.
To me, this was the most enticing. Prior to my migration to WordPress, I had not had a lot of experience with EAV storage. The concept was a little odd to me until I got used ‘the WordPress way.’
This solution involves redefining the way in which meta data is stored within WordPress. Well it least part of it anyway.
After getting used to the EAV model and WordPress’ built in meta functions, I began to see possibilities of how I could store basically whatever I wanted to and to retrieve this data back with a single call to the get_post_meta() or get_user_meta() functions without thinking too much about the draw backs. I could even query this data by passing a few additional arguments in a new WP_Query object. It was so simple. However, as my database began to fill up I quickly began to see a drop in performance, especially when I was querying meta values from the meta tables.
After I investigated ‘the WordPress way’ it left me unsatisfied, so I began playing with idea of re-routing some of my meta data from an EAV vertical structure to a horizontal storage structure and use an intermediary to extract the data from the horizontal structure rather then the EAV structure when required. This data included the data that I regularly need to query that is stored in the meta data tables.
I decided to focus on this and turn this into a plugin that would manage and somewhat automate this process. The plugin is called Horizontal Meta and is not yet publicly available. However, reach out to me if your interested in obtaining it.
Let me emphasise the fact that this method should be used only with proper planning. It’s not necessarily a replacement for proper use of taxonomies, especially if you are dealing with posts, however it may be an alternative if you are dealing with users as WordPress, currently, does not natively support user taxonomies.
I have successfully implemented this technique within a few of my projects now and the results have been pleasing.
The solutions discussed above are not necessarily complete in themselves. In different contexts and circumstances, it may be the right solution to implement any two of the above solutions or even all three. This will solely depend on the scope of your project and its requirements.
In conclusion it is absolute crucial to your projects success that proper preparation and planning is done prior to its commencement. After a project is complete, sometimes it can be too late to go back to the drawing board. Proper preparation will help prevent those frustrating and sleepless nights when your website isn’t working as expected.
At present, I will not be releasing ‘Horizontal Meta’ to the general public. I will be granting conditional licenses in conjunction with a performance review of your WordPress website. Please contact me if you interested in this.
I have released a free version of the plugin which you can download from http://www.horizontalmeta.com/