Post Meta Abuse

Post meta queries bring down databases, cripple traffic scaling, and dramatically inflate page load times. Thankfully there’s an almost trivial solution.

Searching for Meta

The problem comes when you need to query for post meta. This is a data design failure. Examples include:

A meta key for posts that should appear on the homepage
Marking a post as belonging to another post to be shown in a list
Bundling posts together into threads
Setting a type, such as the type of fabric of a post of type t-shirt
Setting an items colour

These are all examples of post meta that we want to search for. We might want to show only the red items, or the t-shirts made of cotton. These sound reasonable, but the query to find those posts with that meta can have monstrous performance costs.

If post meta was built to be searched, we’d be using it to store categories and tags.

Isn’t this just good/bad practice pedantry?

No, sites have been brought down by this, and it’s the reason a number of popular plugins don’t scale to high traffic. Install Query Monitor on a slow site, and you’ll see these queries. Page builders and meta box frameworks are particularly prone to enabling these kinds of queries.

Working at WordPress.com VIP, we see sites with meta queries handle poorly, and aren’t stable with medium/high traffic. Even in local environments, refactoring meta queries dramatically improves page load times. I’ve seen busy sites fall over after introducing simple meta queries.

This isn’t best practice or poor indentation. Meta queries cost real time and money. Adding more servers and faster CPUs won’t fix the problem because it’s a fundamentally expensive thing to do.

Can We Cache this?

Yes and no…

Caching will improve the best possible outcome, but you still have to run the query at least once. Coupled with timing issues your problem won’t be as big but you’ll still have a problem.

All of this is irrelevant though if your queries are too slow to finish, it’ll never complete in time to be cached

Can I Adjust The Database?

Yes and no..

You can add an Index to the post meta table, but this isn’t a true fix because:

the index will only store the beginning of the meta value, this works well for simple meta values such as a number, but for anything else it will be just as slow
the more you store the less efficient the index, storing the entire meta value negates the point of the index
It’s still slow and gives a less than stellar speed bump
You have to create the index yourself, requiring technical skill many don’t have ( some SQL foo )

A meta index may help if your meta value is a short numeric ID, but the gains are minimal

The Easy Fix

The problem is that these meta values should be terms in a custom taxonomy. Taxonomies are built to organise things and provide a way of filtering down to a specific set of posts. Post meta should only be used for information that isn’t going to be searched of filtered for.

Lets take our previous examples and look at how a custom taxonomy would be used:

Indicating that a post should appear on the homepage
The best way to do this is to have a custom taxonomy that controls where the post is shown, that has the homepage checked by default. A lot of people use post meta, and then go one step further by using post meta to say what they don’t want on the homepage. Those queries are orders of magnitude worse than a simple post meta query, and hideously expensive.
Marking a post as belonging to another post to be shown in a list, or bundling posts together into threads
A thread taxonomy where the name of each term is the Post ID of the original post, or a named thread. This taxonomy could be a hidden taxonomy with no UI that works behind the scenes
Setting a type, such as the type of fabric of a post of type t-shirt
A Fabric material taxonomy
Setting an items colour
A colour taxonomy with terms for each colour

But What’s The Catch?

The upfront cost of converting your data from post meta to terms is the main downside. To do this I would use a WP CLI command to convert the data. Keep the post meta data around so that you can compare the two queries.

A taxonomy query is both simpler to write, significantly faster to run, and comes with free benefits, such as:

A database layout that’s optimised for these kinds of queries
The queries are much easier to type
A free UI in the admin interface with menu options
Term meta
Easy REST API support
A free set of archives, I have a talk taxonomy and you can see all my WordPress talks here, or all my WP CLI talks
A set of theme templates, taxonomy.php, taxonomy-{taxname}.php, taxonomy-{taxname}{termID}.php, etc

Taxonomies simplify things while providing plenty of benefits.

21 responses to “Post Meta Abuse”

Jeremy

December 5, 2016

Super interesting post, thank you!

I must admit, up until now I used to only consider taxonomies whenever the data I wanted to save was very similar to categories or tags. For everything else, I used post meta. I’ll try to change that!

I have a few questions, though:

1) What do you do for post meta that is usually saved as an array, with multiple values? This little plugin of mine for example stores 2 different colours for each post. Would you suggest creating 2 different taxonomies?

2) It’s relatively easy to create custom meta boxes in the admin, allowing one to create user-friendly input fields for custom meta data. To take the example above, I add a colour picker to the post editor. What do you do when data has to be saved as a taxonomy term?

1. Tom J Nowell
  
  December 5, 2016
  
  Thanks 😀
  
  1) I must admit, exact hex or RGB value colours don’t lend themselves to terms, you’d end up with hundreds of terms that have a single post, so it’s a case of precision and use case.
  
  The same is true of prices, you wouldn’t search for $4.98, but you might want to show all products between $1 and $5. I’d do this by adding a price range taxonomy, defining my terms as buckets, then putting products in each bucket when they’re saved/updated.
  
  The same strategy might work for colours, but you’d need some rules about how to define those buckets.
  
  But if on the other hand you mean an example of ‘red & blue’ or ‘green & orange’, I’d have a non-hierarchical taxonomy and use a tax_query to filter.
  
  TLDR: Store it as a post meta for precision when displaying on the page or doing calculations, but store a more general version as a term that you can filter on for searching and browsing
  
  2) While I’m not sure that the data a colour picker generates is best stored in a term, creating a metabox should be just as easy as post meta but using `wp_set_post_terms` etc instead.
  
  Admittedly people have had a lot more time and incentive to build meta box frameworks, but one of the more popular and well tested/scalable frameworks Field Manager by Alley Interactive supports this. When defining a field, tell it to use a term datasource object. Coincidentally there’s a Colourpicker field.
  
Justin Sternberg

December 5, 2016

Our friend Alex King (RIP) had some similar advice ~5 years ago: http://alexking.org/blog/2011/08/29/wordpress-post-meta-taxonomies

Good stuff, and thank you for sharing based on your experience.

1. Tom J Nowell
  
  December 5, 2016
  
  Nice post, I’m going to add it to the further reading section 🙂
  
Konstantin Kovshenin

December 6, 2016

Indexes on meta_value won’t really help with WP_Query, because internally WordPress does CAST(), which needs to read and convert all values, before filtering any of them out, which means the index can’t really be used, unless you hack into the generated queries and try to strip away the CAST(), in which case you may have other problems when working with numeric values.

Also surprised you haven’t mentioned Elasticsearch on Sphinx. That would go under a “Not so easy fix” heading, but once it’s all set up, it works like a charm, especially with 10up’s ElasticPress plugin.

Dave

December 6, 2016

So from reading both yours and Alex’s articles, would it be right to say that it’s fine to use custom fields (post meta) for elements on a page which won’t be searched for – ie Phone Number, Gallery, link to external website, Google Map etc? But then to use custom taxonomies for elements which will be queried – ie to display all posts which are “Self Catering or B&Bs”, “Dogs Welcome/Not Welcome” or “Has Wifi”?

Nick Ciske

December 7, 2016

We recently released a plugin to automatically fix this issue for location data, which is a major offender of post meta abuse:

https://wordpress.org/plugins/wp-geometa/

Patricia BT

December 7, 2016

Hi Tom,

This is the kind of posts I LOVE to read, just perfect for people like me = not developers, but able to understand when explained clearly 🙂 and I also like the fact that the “you shouldn’t” is followed by the “why” … thanks a lot!

Are you on your gravatar with Finnish’ Moominpappa? If yes, I must friend you somewhere on social networks absolutely 😉

Cheers!

1. Tom J Nowell
  
  December 7, 2016
  
  Thanks 🙂 It’s from the Moomin Café in Hong Kong, they sit you down with giant moomins while you eat
  
  1. Patricia BT
    
    December 7, 2016
    
    Hong Kong!! Good to know 🙂 Thx
    
Dave Green

December 10, 2016

Great post Tom. Both eye opening and frustrating in equal measure, as the more performant solution is right under our noses!

Jamie

December 22, 2016

Great timing of this post – it made me realise I should be using a taxonomy instead of custom field for a project I’m working on at this very moment, and inspired me to optimize another query to be way less complex. Thank you! 🙂

Deepti Boddapati

April 27, 2017

I noticed this, but I didn’t know how to say it. It’s so hard to communicate how this stuff affects businesses, without sounding myopic and pedantic. Thanks for writing this, it’s really well explained.

To ease the friction of converting meta values to terms maybe this snippet can be helpful:
https://github.com/deeptiboddapati/convertmetatotaxaterms

How to vet a plugin for add on development – Austin WordPress Meetup

May 4, 2017

[…] How post meta gets abused […]

Custom Fields vs. Custom Taxonomies, when to (not) use? – Meta Box

July 11, 2017

[…] Another thing that you should consider when structure your data: query performance. Because of the WordPress database structure, querying posts by taxonomy is faster than querying by custom fields. Queries by taxonomy are well optimized as this is a primary front-end presentation feature in WordPress core. Conversely, querying by custom field key and value is slow. The value column in the custom field table is not indexed – you are basically doing a search through data that is not intended for that purpose. Even when you add an index to the post meta table, it won’t fix. […]

Lewis Cowles

July 13, 2017

Isn’t part of the problem with saying “use taxonomies for search” that they still need meta-data to be useful for searching more than a title because these are general purpose objects?

On pricing, I’m particularly worried that categories might be a lower-effort fit, but certainly not a best-case (for the end-user) fit.

As an alternative, you could instead of using a direct meta query, simply wrap these expensive queries in a pre-primed cache for the most common search. By separately priming the cache (perhaps a curl call to a REST endpoint, or admin-ajax), you can relatively swiftly get the result you like.

Alternatives would be some method (I’ve seen lots of them), connecting to a copy of the database in a master->slave setup (I don’t particularly like the terminology, but it’s whats in the mysql manual). This would also allow you to spread the load (search is always going to be read-only).

I’ll concede logging searches then becomes another discussion, but by thinking in blocks of what you want to do, you become less restrictive about how you use WordPress, and open yourself to more options.

1. Tom J Nowell
  
  July 25, 2017
  
  No because so are post meta key value pairs. You can still make a key value pair that’s searchable, except the key is the taxonomy, and the value is the term. It’s a matter of imagination, and deciding the trade off. A lot of people are unaware they’re making a trade off with performance and scale as the cost they’re paying.
  
  As for caching, the problem there is it assumes the server can finish the query to begin with. Cache stampedes and race conditions also factor in. As for database load, adding slave databases could get expensive very quickly, especially at scale, but it’s still fundamentally a slow query even in the most optimistic of situations ( a fast server with a single visitor )
  
  I’m also unsure if you mean the same thing when you say search. I mean it as in a database finding posts given X Y and Z, rather than me typing a search term into a search box, or using the ‘s’ query variable in WP_Query. If you need to log post queries or user searches, there are dedicated systems for that such as elastic search + Kibana to name one
  
Ivan`

September 16, 2021

This post will save my client time and money – real money. One problem is using dynamic.ooo “view” element in elementor which is just a grid basically they dont allow you to orderby a taxonomy. 🙁

Ollie Jones

February 9, 2022

I’m late to this … still, with modern MySQL / MariaDB it’s possible to index wp_postmeta to overcome many of these problems. Here’s a plugin to do that. https://wordpress.org/plugins/index-wp-mysql-for-speed/

Hierarchical taxonomy queries (like in WooCommerce) are still a big problem.

1. Tom J Nowell
  
  February 9, 2022
  
  At most indexes mitigate the problem, they don’t solve it. Indexes can help with edge cases, but you can’t keep adding indexes until all the problems go away. If that were the case there’d be no reason for indexes in the first place, indexes have a cost. It’s also not an excuse to just keep using post meta for absolutely everything.
  
  There is a tradeoff here and most people are unaware of it with major performance implications. You can still write expensive multi-dimensional queries using tax_query, that doesn’t mean taxonomies have no value from a performance standpoint.
  
plugin development – Add column and post filter for a custom post type field on the edit.php page – Yoursharelink

October 19, 2022

[…] alternative of meta could make put up queries extra environment friendly. Extra on that right here https://tomjn.com/2016/12/05/post-meta-abuse/ by WPSE member Tom J […]