Performance degradation when loading taxonomy terms

Created on 9 June 2025, about 2 months ago

Problem/Motivation

Exposed filters for taxonomy terms in Views can trigger inefficient SQL queries with multiple joins, resulting in slow performance β€” particularly in multilingual environments with large vocabularies.

Steps to reproduce

1. Create a new taxonomy Tags, if it does not already exist.
2. Add several new terms to the Tags taxonomy.
3. Add a reference field to the Tags taxonomy in the Article content type, if it does not already exist.
4. Create a new View.
5. Under Filter criteria, click Add:
1. Select Tags (field_tags)
2. Set the selection type to Dropdown
3. Check Expose this filter to visitors, to allow them to change it
4. Choose Is one of as the Operator
6. Set a breakpoint on the line $terms = Term::loadMultiple($query->execute()); in the file web/core/modules/taxonomy/src/Plugin/views/filter/TaxonomyIndexTid.php:225
7. Enable Xdebug.
8. Click Update preview in the View.
9. Verify that the generated SQL looks like the following (a `status` field may also be included):

  SELECT
    "base_table"."revision_id" AS "revision_id",
    "base_table"."tid" AS "tid",
    "taxonomy_term_field_data_2"."weight" AS "weight",
    "taxonomy_term_field_data_2"."name" AS "name"
  FROM
    "taxonomy_term_data" "base_table"
    INNER JOIN "taxonomy_term_field_data" "taxonomy_term_field_data" ON "taxonomy_term_field_data"."tid" = "base_table"."tid"
    LEFT JOIN "taxonomy_term_field_data" "taxonomy_term_field_data_2" ON "taxonomy_term_field_data_2"."tid" = "base_table"."tid"
  WHERE
    "taxonomy_term_field_data"."vid" = 'tags'
  ORDER BY
    "taxonomy_term_field_data_2"."weight" ASC,
    "taxonomy_term_field_data_2"."name" ASC

Such SQL query can take an inefficient amount of time to fetch the required taxonomy terms. For example, if there are 30 languages and 240 terms, the query may retrieve more than 216,000 rows instead of just 240.

Proposed resolution

A simpler and more efficient SQL query can be used to fetch all necessary terms:

  SELECT ttfd.tid AS tid
  FROM taxonomy_term_field_data ttfd
  WHERE (default_langcode = 1) AND (status = '1') AND (vid = 'tags')
  ORDER BY ttfd.weight ASC, ttfd.name ASC

To further improve performance, consider adding the following index:

  CREATE INDEX taxonomy_term__vid_default_langcode_status ON taxonomy_term_field_data (vid, default_langcode, status)

This index significantly speeds up queries filtering by vocabulary, language, and status.

πŸ“Œ Task
Status

Active

Version

10.4 ✨

Component

taxonomy.module

Created by

πŸ‡ΈπŸ‡ͺSweden devdits

Live updates comments and jobs are added and updated live.
  • Performance

    It affects performance. It is often combined with the Needs profiling tag.

Sign in to follow issues

Comments & Activities

Production build 0.71.5 2024