meta_query in WP_Query: How Each Clause Becomes a Join and What It Costs

A page that filters posts by three custom fields is one array in PHP and four joins in SQL. The array reads like a filter; the SQL reads like a table being visited once for every condition, because that is exactly what it is.

The interesting part is not that it is slow – most sites never grow large enough to care. It is that the cost is invisible in the code that causes it, and that it grows with two things at once: the number of clauses and the size of a table nobody looks at.

What WP_Query Builds Out of the Array

Each clause in meta_query gets its own alias on wp_postmeta and its own join condition. Two clauses, two aliases; five clauses, five. The table is joined to itself as many times as there are conditions, and every one of those joins carries the full weight of the table behind it.

SELECT wp_posts.ID FROM wp_posts
  INNER JOIN wp_postmeta pm1 ON wp_posts.ID = pm1.post_id
  INNER JOIN wp_postmeta pm2 ON wp_posts.ID = pm2.post_id
  INNER JOIN wp_postmeta pm3 ON wp_posts.ID = pm3.post_id
WHERE post_type = 'post' AND post_status = 'publish'
  AND ( pm1.meta_key = '_region'  AND pm1.meta_value = 'north' )
  AND ( pm2.meta_key = '_level'   AND pm2.meta_value = 'b' )
  AND ( pm3.meta_key = '_active'  AND pm3.meta_value = '1' )
ORDER BY wp_posts.post_date DESC

Nothing here is wrong. It is the literal translation of what was asked for, and the database will answer it correctly – after reading, on a modest site, something in the order of half a million rows to return forty.

The Index That Does Not Exist

wp_postmeta carries three indexes: the primary key on meta_id, one on post_id and one on the first 191 characters of meta_key. There is no index on meta_value, and there cannot usefully be one: the column is LONGTEXT, and an index on a text column of unbounded length is either a prefix or nothing.

So every clause is answered in two movements. The join finds the rows for a post by post_id – fast, indexed, exactly what an index is for. Then the value is compared inside each of those rows, one at a time, with no help from anything. The work is proportional to how many meta rows a post has, which on a site with a page builder and a few plugins is rarely under thirty.

The Clauses That Cost More Than the Others

The last row is the one that turns a slow page into a timeout. Sorting by a meta value cannot be done from an index, so the database materialises the whole intermediate result and sorts it – and because meta_value is LONGTEXT, that sort happens on disk rather than in memory as soon as the set is large.

NOT EXISTS deserves its own warning. It reads as the cheap opposite of EXISTS and is the opposite of cheap: proving that something is absent means visiting every candidate, which is the one thing an index cannot shorten.

Where the Filter Belongs Instead

A value that is filtered on is not a property of a post in the way a subtitle is. It is a category, and WordPress already has a structure for categories with the right indexes: wp_term_relationships is indexed on both of its columns, so a term filter is one join answered entirely from indexes, with no text comparison anywhere.

The rule that follows is short. Anything used for filtering or sorting belongs in a taxonomy – or, when the values are truly numeric and continuous, in a column of its own table. Anything only ever read after the post has been found can stay in post meta, where it costs a single lookup by post_id and nothing else.

Measuring Instead of Guessing

The query built by WP_Query is available before it runs: $query->request holds the finished SQL, and running it through EXPLAIN answers the only two questions that matter. The rows column is the estimate of how many rows each step will read; the Extra column names the expensive shapes outright, and Using temporary; Using filesort together are the signature of a sort that will not scale.

wp db query "EXPLAIN SELECT wp_posts.ID FROM wp_posts INNER JOIN ..."

id  select_type  table     type    key       rows    Extra
1   SIMPLE       wp_posts  ref     type_st.  4800    Using temporary; Using filesort
1   SIMPLE       pm1       ref     post_id     40    Using where
1   SIMPLE       pm2       ref     post_id     40    Using where
1   SIMPLE       pm3       ref     post_id     40    Using where

Three rows saying Using where after a post_id lookup is the picture described above: the index found the rows, and the condition was applied afterwards. The number in rows multiplied across the joins is the figure that grows with the site, and it is worth knowing on the day the query is written rather than on the day the page stops loading.