Diagnose slow LEFT JOIN queries in WordPress

On large installations, a WordPress query that filters by categories or taxonomies can take a long time when it combines posts and term_relationships, sorts and limits results. The presence of LEFT JOIN does not prove on its own that the query is wrong: data, filters, indexes and the execution plan all matter.

Background to the historical case

This guide came out of incidents with hundreds of thousands of posts and refers to WordPress ticket 54346, which studies category queries and alternatives using subqueries.

The results observed on certain installations do not guarantee millisecond times for any data volume. Nor do they turn a patch from the ticket into an official fix that applies to every version.

Identify the real query

1. Reproduce a slow URL and record whether there was a session, cache and filters.
2. Use PHP X-Ray to find the complete query and the component that generates it.
3. Note the versions of WordPress, MySQL or MariaDB and the plugins involved.
4. On a copy with representative data, review the plan with EXPLAIN and the existing indexes.

Compare how many rows are examined, where filtering happens and how results are sorted. A different plan after an update deserves analysis, but it does not automatically identify a single culprit.

Assess an alternative

The developer can try a subquery, an equivalent expression, an index or a rework of how WP_Query is used. Check that it keeps the same posts, order, exclusions, duplicates and pagination, especially with combinations of taxonomies.

Do not replace every JOIN or remove GROUP BY globally. A change that speeds up one category can return wrong results in another query.

Apply and maintain

Avoid editing the core files directly: updates overwrite the changes and an old patch may stop fitting. If a temporary adaptation is essential, the developer must maintain it with version control, tests and a way to remove it.

After the change, compare several runs and check categories, searches, pagination and the admin area. Keep the previous plan and revert if other operations get worse. For queries that also calculate totals, see the SQL_CALC_FOUND_ROWS guide.