Alter the main query to use EXISTS rather than LEFT JOIN

Available since 6.32. Off by default.

WordPress filters products by taxonomy, such as a category, an attribute or WooCommerce’s product visibility, either by joining wp_term_relationships to the posts query or with a NOT IN subquery. This option rewrites the clauses it can as EXISTS and NOT EXISTS instead. Together with Optimise WooCommerce Group By, that lets the query drop its GROUP BY entirely when no joins are left.

The option changes how the SQL is written, never what it returns. The same products appear, in the same order, with the same totals, whether it is on or off.

Scalability Pro exposes this as Alter main query to use EXISTS rather than LEFT JOIN under Advanced > Query rewrites. Tick it and save.

Why a join needs a GROUP BY

A LEFT JOIN to wp_term_relationships produces one row for every matching relationship, not one row per product. A product that matches two of the requested terms comes back twice. To hand the theme a list of distinct products, WordPress adds GROUP BY wp_posts.ID whenever the query carries a taxonomy clause, and the database has to group the whole result before it can sort and page it.

EXISTS asks a different question: for each product, is there at least one matching relationship? The answer is yes or no, so each product appears once and there is nothing to de-duplicate.

Here is WooCommerce’s rating filter on the shop page, with the option off:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1 AND (
  wp_term_relationships.term_taxonomy_id IN (13)
  AND wp_posts.ID NOT IN (
    SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (7)
  )
) AND ((wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish')))
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC
LIMIT 0, 16

And the same page with this option and Optimise WooCommerce Group By both on:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
WHERE 1=1 AND ((wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish')))
AND ((EXISTS (SELECT 1 FROM wp_term_relationships tr
              WHERE wp_posts.ID = tr.object_id AND tr.term_taxonomy_id IN (13)))
 AND (NOT EXISTS (SELECT 1 FROM wp_term_relationships tr
                  WHERE wp_posts.ID = tr.object_id AND tr.term_taxonomy_id IN (7))))
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC
LIMIT 0, 16

The join is gone, so there are no duplicate rows, and the GROUP BY is gone with it. Term 13 is the rating and term 7 is WooCommerce’s hidden-from-catalogue term; the IDs differ on every site.

Where the rewrite runs

Only on the front-end main query: the query that decides which products a shop, archive or search page lists. It never runs on:

• wp-admin screens
• AJAX requests
• cron
• secondary queries, such as widgets, related products, shortcodes and block product grids

Imports are therefore unaffected. WP All Import, Super Speedy Imports, WP-CLI and REST API requests all run outside the front-end main query, so they never go through the rewrite.

Which clauses it converts

The rewrite reads the main query’s tax_query as it stands when WordPress fires pre_get_posts, and converts a clause only when it can keep the clause’s exact meaning:

• a clause whose field is term_taxonomy_id, with the IN, NOT IN or AND operator, becomes an EXISTS or NOT EXISTS predicate;
• a multi-term AND becomes one EXISTS predicate per term, so every term must match;
• an OR group is rewritten only when every branch in it can be converted, because converting half of an OR would turn it into an AND.

On a WooCommerce store, that means two clauses:

• Product visibility. WooCommerce adds this check to every catalogue page (shop, category, tag, attribute, search and filtered pages) to leave out hidden products and, when Hide out of stock items is on, out-of-stock products. It is written as a NOT IN subquery and becomes NOT EXISTS. The products it leaves out are the same.
• The rating filter, which becomes EXISTS.

These keep their join, and the reason for each:

• The archive’s own category, tag or attribute. WordPress keeps the term from the URL in its own query variable, such as product_cat, and merges it into its tax query internally, so it never appears in the tax_query variable the rewrite reads.
• Category clauses that include child categories. WordPress adds the child terms after the rewrite has run, so converting the clause early would leave the children out.
• WooCommerce’s layered navigation attribute filters. They match terms by slug, not by term_taxonomy_id.
• Pages Super Speedy Filters serves from its own index. Super Speedy Filters builds that taxonomy SQL itself, so the rewrite leaves it alone.
• OR groups with a branch that cannot be converted, for the reason above.

The option does not remove sorting and it does not rewrite meta-query joins.

How it pairs with Optimise WooCommerce Group By

Optimise WooCommerce Group By looks at the finished query. If it selects only columns from wp_posts and has no joins, it removes GROUP BY and DISTINCT entirely. If joins remain, it replaces GROUP BY with DISTINCT, which removes duplicates without grouping.

WordPress applies this option’s EXISTS predicates while it builds the WHERE clause, and only then hands the finished query to the Group By optimisation. So the Group By optimisation sees the query after the joins this option removed are already gone.

On every page in every state, the products, their order and the totals were identical.

Super Speedy Filters leaves out hidden products through its own index and drops WooCommerce’s visibility check when its index already covers it. When Super Speedy Filters serves a page from that index, this option leaves the page’s taxonomy SQL to Super Speedy Filters, so the query is exactly the one Super Speedy Filters builds.

What to test before enabling

The rewrite should never change which products appear. These checks confirm that on your site. Test on staging: for each page below, note the product count and the order of the first page with the option off, then turn it on and compare. They should match exactly.

If every count and order matches, leave it on. If one doesn’t, turn it off and report which page and which filters produced the difference.

Further reading

Related products

Scalability ProSpeed up your whole site – big catalogues, big databases, no re-architecture