There are two types of indexes available for meta query usage on VIP:
- By default, WordPress’ postmeta table comes with an index on
- On the VIP platform, we’ve added an index on
vip_meta_key_value. Queries must include both key and value in the
WHEREclause. The index is truncated at 191 characters for
meta_keyand 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
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 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” to check for the
meta_value “true”. The solution is to change this so that the 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, 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 “
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 “
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.
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 cause severe performance problems based on how Elasticsearch stores the data, not how it queries the data.
Last updated: October 26, 2023