add paragraph about meta_queries and NOT_EXISTS #356
Conversation
moraleida
changed the title
|
@bengreeley would appreciate review from you on this, thanks! |
| [Post meta](https://wordpress.org/support/article/custom-fields/) lets us store unique information about specific posts. As such the way post meta is stored does not facilitate efficient post lookups. Generally, looking up posts by post meta should be avoided (sometimes it can't). If you have to use one, make sure that it's not the main query and that it's cached. | ||
| * [Post meta](https://wordpress.org/support/article/custom-fields/) lets us store unique information about specific posts. As such the way post meta is stored does not facilitate efficient post lookups. Generally, looking up posts by post meta should be avoided (sometimes it can't). If you have to use one, make sure that it's not the main query and that it's cached. | ||
|
|
||
| * Always set default values for post_meta keys, especially if they are used to filter other queries. When writing queries filtering by post meta, never use the compare type ```NOT EXISTS```. |
There was a problem hiding this comment.
@moraleida Could you put an example of when we'd set default values for post_meta keys? Is that when using update_post_meta or add_post_meta? I feel like we need a bit more information on why default values (instead of empty strings, I assume) are a best practice.
There was a problem hiding this comment.
I'd recommend we lead with 'Never use compare type NOT EXISTS' as the leading part of this (or on its own line) since it tees up the majority of the rest of the section
|
|
||
| Queries that need to check if a given meta_key does not exist need to use `LEFT JOIN`, gathering a lot more data. In practice, this type of query destroys any other performance improvements because it requires the MySQL engine to run through all the rows in the selected set to make sure it catches any rows that meet the criteria. | ||
|
|
||
| If all posts have a specific meta key, or if you can test for the presence of a given key, the engine can use a simple `INNER JOIN` and only touch the rows that already meet the criteria, saving a lot of time and processing on large tables. |
There was a problem hiding this comment.
I'd recommend rewording If all posts have a specific meta key, or if you can test for the presence of a given key to To search for the presence of a given key. The 'if all posts have a specific meta key' doesn't make a ton of sense to me, but I could be missing something.
There was a problem hiding this comment.
@moraleida Thank you for putting this together! I made a few suggestions in wording that will help clarify the intent and really focus on the performance recommendations. Let me know if you have any questions.
Description of the Change
Fixes #355
Adds a paragraph discouraging the use of "EXISTS" or "NOT_EXISTS" as comparison methods to query for post meta. Please see full description as examples in the Issue.
Checklist:
Applicable Issues
#355
Changelog Entry