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
meta_key+meta_value. To use this index, called
vip_meta_key_value, queries must include both key and value in the
WHEREclause. One thing to note is it 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
There are many use cases that 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 “
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 potentially cause severe performance problems based on the way Elasticsearch stores the data and not how it queries the data.