Problem/Motivation

TaxonomyIndexDepthQueryTrait::addSubQueryJoin() builds one UNION branch for each configured depth level. Each branch adds another join to taxonomy_term__parent, and the pager count query executes the subquery a second time.

Query cost therefore grows with the configured depth, even when the actual taxonomy hierarchy is much shallower. Site builders commonly configure a higher maximum depth than their current hierarchy requires.

In a benchmark with 50,000 terms, 10,000 nodes, and a selected term with 119 descendants in a four-level hierarchy:

  • Depth 4 completed in ~4 ms.
  • Depth 9 completed in ~102 ms.

Both configurations returned identical results.

This issue supersedes #1358412, where the benchmark results and scripts were first posted.

Steps to reproduce

  1. Generate the benchmark data using Devel Generate.
  2. Run the attached benchmark scripts at depths 4 and 9.
  3. Compare the execution times and confirm that both queries return the same results.

See #1358412-105: Content: Has taxonomy term ID (with depth) query performance and #1358412-106: Content: Has taxonomy term ID (with depth) query performance for the scripts and full benchmark details.

Proposed resolution

Expand the matching taxonomy term IDs in PHP before building the Views query:

  1. Query taxonomy_term__parent once per hierarchy level.
  2. Stop when a level adds no new term IDs or the configured depth is reached.
  3. Join taxonomy_index against the resulting flat list of term IDs.
  4. Use DISTINCT to prevent duplicate results.

This makes the work depend on the actual hierarchy depth rather than the configured maximum depth. In the existing benchmark, depth 9 then performs the same work as depth 4 and completes in approximately 8 ms total, with an identical result set.

Remaining tasks

  • Benchmark a subtree containing thousands of descendants to measure the effect of a large IN condition.
  • Add kernel test coverage.

Issue fork drupal-3610700

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

About issue forks

Comments

joelpittet created an issue. See original summary.

joelpittet opened merge request !16315

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Status: Active » Needs review
joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Status: Needs review » Needs work
Issue tags: +Needs issue summary update

I currently have both plugins and for the end user that will be confusing (as @alexpott pointed out in the MR comment), I just have this like this for a quick side-by-side test, though a branch change would probably be just as quick during the test.

Setting to needs work, to remove that, and focus on the complexity of the change for the diff

sjpagan’s picture

sjpagan commented

Alternative to the proposed resolution: skip the union branches that cannot match. A branch with N joins needs a chain of N parent-child relationships, so past the real hierarchy depth it returns nothing. Within the hierarchy the query is unchanged.

On 50k terms over four levels, depth 9 goes from 54 branches to 9, and from 75.9 ms to 24.0 ms on the largest subtree, same rows. Cost is one cached lookup per query build, 0.3 ms.

Numbers from a local run: MariaDB 10.11, PHP 8.5, 50k terms, 10k nodes, four hierarchy levels.

No second handler, no schema change. Branch 3610700-real-hierarchy-depth on the issue fork. @joelpittet, separate MR or fold into !16315?