Rewrite LEFT JOINs to WHERE EXISTS
When you filter an archive by taxonomy or by a meta value, WordPress builds the query using LEFT JOINs against wp_term_relationships and wp_postmeta. That’s a reasonable default, but it doesn’t scale well. With large tables, MySQL has to build a big intermediate join result, matching your posts against every relevant row in those tables, before it can filter the result down. The more terms and meta rows you have, the more work MySQL does, and the slower the page gets.
The problem is that a LEFT JOIN used purely to test “does a matching row exist?” makes MySQL do more than it needs to. It joins everything together first, then filters, when all you really wanted to know is whether at least one matching row is present.
The Query Speed tab has an option for this: “Alter main query to use EXISTS rather than LEFT JOIN”. When enabled, it rewrites those joins into WHERE EXISTS subqueries. The advantage is that a WHERE EXISTS subquery can stop the moment MySQL finds a single matching row. It doesn’t need to build or scan a full join result, so on large tables these queries run much faster. The bigger your wp_term_relationships and wp_postmeta tables, the more you gain.
This pairs naturally with Remove SQL_CALC_FOUND_ROWS. One stops MySQL counting the whole result set, the other stops it building a large join to filter in the first place. On a big filtered archive, enabling both together is where you see the biggest drop in query time.
This is an advanced option because it changes how the main query is constructed. In the vast majority of cases the results are identical, but query rewriting can occasionally interact with unusual filter combinations or third-party code that hooks the query. So after enabling it, test your archives, category pages and any faceted filtering to confirm they return what you expect.
The rewrite is designed to return identical results, and in almost every case it does. The rare exceptions are unusual filter combinations or third-party code that hooks the main query directly. Walk through your archives, category pages and any faceted filters once after switching it on, and you’ll catch any interaction quickly.
In short: WordPress uses LEFT JOINs for taxonomy and meta filtering, which forces MySQL to build large intermediate results. This option in the Query Speed tab rewrites them as WHERE EXISTS subqueries that short-circuit as soon as a match is found. Enable it, then test your archives.
Comments