A quick rule of thumb to know if querying on
meta_value will be a problem is to ask: “Will I be querying for this
meta_value using WP_Query?”
If the answer is no, then you’ve got a perfect use case for postmeta values.
If the answer is yes, then the query is likely to have issues with performance and scalability, because the WordPress postmeta table has an index on
meta_key, but not
Avoiding performance problems
Many use cases can be modified to avoid performance problems:
meta_value queries can be transformed into taxonomy queries. For example, instead of using a
meta_value to filter if a post should be shown to visitors with membership level “Premium”, use a custom taxonomy and a term for each of the membership levels in order to leverage the indexes.
meta_value is set as a binary value (e.g. “
hide_on_homepage” = “true”), MySQL will look at every single row that has the
hide_on_homepage” in order to check for the
meta_value “true”. The solution is to change this so that the mere presence of the
meta_key means that the post should be hidden on the homepage. If a post shouldn’t be hidden on the homepage, simply delete the “
meta_key. This will leverage the
meta_key index and can result in large performance gains.
Instead of setting
meta_key equal to “
meta_value equal to “sports”, you can set
meta_key to “
primary_category_sports“. This enables you to query by
primary_category. However, instead of doing
get_post_meta( $id, 'primary_category’), you would need to iterate over possible values of
get_post_meta( $id, 'primary_category_sports’). If you need to do both, you could use a “
primary_category” and a “
meta_key that both update when the primary category changes. Another, better, solution for this particular use case would be to use a hidden taxonomy named
primary_category and have the categories be the terms.
If it’s not possible to avoid performing a
meta_value query, consider using Elasticsearch instead of MySQL.
One caveat to note is that if you are using Elasticsearch on your site (regardless of whether it’s for a particular query or just in general) having multiple distinct
meta_keys such as the example of non-binary situations could potentially cause severe performance problems. This is based on the way Elasticsearch will store the data and not how it queries the data and therefore it doesn’t matter if you are using Elasticsearch for that particular query or not.