[5.x]: Relationship queries with targetElement are highly inefficient

What happened?

Description

We've had persistent performance issues on a particular site and identified one particular query as the culprit. The query is used to find related entries. Related entries in this case means that we want to find entries that have all the same entries selected across multiple Entries fields.

We found that the query is super slow and gets exponentially slower with the amount of selected entries. However, the issue only occurs when we explicitly specify the direction of the relation using targetElement instead of just passing the element IDs.

I boiled it down to a minimal example. Consider the following queries:

{% set related_category_ids = [
    1719,
    1699,
    1746,
    1747,
    317819,
    1750,
    1722,
    317921,
    1724,
    317947,
] %}

{# Slow, but acceptable (~300 ms) #}
{% set related_entries = craft.entries()
    .relatedTo(['and', ...related_category_ids])
    .limit(4)
    .all()
%}

{# Exceedingly slow (~30 s) #}
{% set related_entries = craft.entries()
    .relatedTo({ targetElement: ['and', ...related_category_ids] })
    .limit(4)
    .all()
%}

{# Also exceedingly slow (~30 s) #}
{% set related_entries_query = craft.entries().limit(4) %}
{% for category_id in related_category_ids %}
    {% do related_entries_query.andRelatedTo({ targetElement: category_id}) %}
{% endfor %}
{% set related_entries = related_entries_query.all() %}

Here are the raw MySQL queries generated for the three examples:

1.sql
2.sql
3.sql

To summarize: Using targetElement instead of leaving the direction open causes the query to be two orders of magnitude slower.

I'll be honest, I don't fully understand why this is the case. Something about the execution plan with the nested subquery.

In any case, I think the query builder should be able to generate a more efficient query here. In theory, looking just at the targetId instead of both targetId and sourceId should take less work. But something about the way the queries are converted to SQL causes an issue with the execution plan here.

Happy to be told if I'm doing something wrong!

Craft CMS version

5.8.18

PHP version

8.3

Operating system and version

No response

Database type and version

MySQL 8.0.40

Image driver and version

No response

Installed plugins and versions

Start by comparing the attached 1.sql, 2.sql, and 3.sql queries, then trace the entry-query paths for relatedTo() and andRelatedTo() with targetElement. Use the reproducible Twig examples against MySQL 8.0.40 and inspect the query plans. Done means the targetElement forms no longer show the reported two-orders-of-magnitude slowdown while preserving the related-entry results.