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.
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.
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.
Active
10.4 ✨
taxonomy.module
It affects performance. It is often combined with the Needs profiling tag.
If this problem was discovered on a version of Drupal that is not 11.x, add that information in the issue summary and leave the version at 11.x. In Drupal core changes are made on on 11.x (our main development branch) first, and are then back ported as needed according to the Core change policies → . Also mentioned on the version → section of the list of issue fields documentation.