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.