Meta queries

There are two types of indexes available for meta query usage on VIP:

1. By default, WordPress’ postmeta table comes with an index on meta_key (but not meta_value).
2. On the VIP platform, we’ve added an index on meta_key+meta_value. To use this index, called vip_meta_key_value, queries must include both key and value in the WHERE clause. One thing to note is it is truncated at 191 characters for meta_key and 100 for meta_value — if you have datasets that share the first 191 characters for the meta key and the first 100 characters for the meta value, the index will be less effective due to returning multiple results.

Avoid queries that only use the meta_value field.

Avoiding performance problems

There are many use cases that can be modified to avoid performance problems.

Taxonomy terms

Some 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.

Binary situations

When meta_value is set as a binary value (e.g. “hide_on_homepage” = “true”), MySQL will look at every single row that has the meta_key “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 “hide_on_homepage” meta_key. This will leverage the meta_key index and can result in large performance gains.

Non-binary situations

Instead of setting meta_key equal to “primary_category” and meta_value equal to “sports“, you can set meta_key to “primary_category_sports“.

The downside to this approach is you cannot simply use get_post_meta( $id, 'primary_category’) and you would need to iterate over possible values of primary_category_* until you find your result (e.g. get_post_meta( $id, 'primary_category_sports’)). If you need to do both, it’s possible to use a “primary_category” and a “primary_category_sports” meta_key that both update when the primary category changes. A better solution for this particular use case would be to use a hidden taxonomy named primary_category and have the categories be terms.

Elasticsearch

Consider using Elasticsearch instead of MySQL if you don’t have too many distinct meta keys.

The combination of using Elasticsearch on your site (regardless of whether it’s for a particular query or just in general) and having multiple distinct meta_keys (e.g. non-binary situations) could potentially cause severe performance problems based on the way Elasticsearch stores the data and not how it queries the data.