当数据增加时，在本地运行0.2秒的查询在AWS MySQL示例上需要20秒。解决方案是添加子查询或提示。
SQL Origin。

SELECT DISTINCT m0_.id AS id_0, m0_.alias AS alias_1 
FROM marketed_name m0_ 
INNER JOIN product p1_ ON m0_.id = p1_.marketed_name_id AND (p1_.active = 1)
INNER JOIN attribute_value a2_
INNER JOIN product_value p4_ ON a2_.id = p4_.value_id 
INNER JOIN product p3_ ON p3_.id = p4_.product_id AND (p3_.id = p1_.id) 
INNER JOIN product p5_ 
INNER JOIN marketed_name m6_ ON p5_.marketed_name_id = m6_.id AND (m6_.alias = 'betametazon') 
INNER JOIN attribute_value a7_ 
INNER JOIN product_value p9_ ON a7_.id = p9_.value_id 
INNER JOIN product p8_ ON p8_.id = p9_.product_id AND (p8_.id = p5_.id) 
INNER JOIN attribute a10_ ON a7_.attribute_id = a10_.id AND (a10_.alias = 'atc') 
LEFT JOIN price p11_ ON p1_.id = p11_.product_id AND (p11_.current > 0) 
LEFT JOIN (product_image p12_ 
LEFT JOIN image i13_ ON p12_.id = i13_.id) ON p1_.id = p12_.product_id AND (p12_.main = 1) 
WHERE p1_.id <> p5_.id 
AND m0_.id <> p5_.marketed_name_id
AND a2_.attribute_id = a7_.attribute_id 
AND a2_.string_value = a7_.string_value 
GROUP BY m0_.id 
ORDER BY m0_.alias ASC LIMIT 30

添加提示

INNER JOIN product p1_ **FORCE INDEX FOR ORDER BY(IDX_34242323423423423)** ON m0_.id = p1_.marketed_name_id

或子查询

SELECT DISTINCT m0_.id AS id_0, m0_.alias AS alias_1 
FROM (
    SELECT m0_.id, m0_.alias
    FROM marketed_name m0_
    INNER JOIN product p1_ ON m0_.id = p1_.marketed_name_id
    ORDER BY p1_.vendor_id ASC
) AS m0_
INNER JOIN product p1_ ON m0_.id = p1_.marketed_name_id
INNER JOIN attribute_value a2_
INNER JOIN product_value p4_ ON a2_.id = p4_.value_id 
INNER JOIN product p3_ ON p3_.id = p4_.product_id AND (p3_.id = p1_.id) 
INNER JOIN product p5_ 
INNER JOIN marketed_name m6_ ON p5_.marketed_name_id = m6_.id AND (m6_.alias = 'azitromicin')
INNER JOIN attribute_value a7_ 
INNER JOIN product_value p9_ ON a7_.id = p9_.value_id
INNER JOIN product p8_ ON p8_.id = p9_.product_id AND (p8_.id = p5_.id) 
INNER JOIN attribute a10_ ON a7_.attribute_id = a10_.id AND (a10_.alias = 'atc') 
LEFT JOIN price p11_ ON p1_.id = p11_.product_id AND (p11_.current > 0)
LEFT JOIN (product_image p12_ LEFT JOIN image i13_ ON p12_.id = i13_.id) ON p1_.id = p12_.product_id AND (p12_.main = 1) 
WHERE p1_.id <> p5_.id AND m0_.id <> p5_.marketed_name_id AND p1_.active = 1 AND a2_.attribute_id = a7_.attribute_id AND a2_.string_value = a7_.string_value GROUP BY m0_.id ORDER BY m0_.alias ASC LIMIT 30

我有一个名为buildIndexAnalogs（）的函数，它使用QueryBuilder对象创建一个查询，该查询返回给定商品名的类似物列表。

public function buildIndexAnalogs(QueryBuilder $qb, string $marketedName): void
    {
        $qb
            ->addSelect('COUNT(DISTINCT p.id) productsCount')
            ->addSelect('MIN(pp.current) minPrice')
            ->addSelect('MAX(pp.current) maxPrice')
            ->addSelect('MAX(pp.updatedAt) maxDate')
            ->addSelect('ANY_VALUE(i.src) src')
            ->addSelect('ANY_VALUE(i.largeSrc) largeSrc')
            ->addSelect('ANY_VALUE(i.mediumSrc) mediumSrc')
            ->addSelect('ANY_VALUE(i.smallSrc) smallSrc')
            ->addSelect('ANY_VALUE(i.width) width')
            ->addSelect('ANY_VALUE(i.height) height')
            ->addSelect('ANY_VALUE(i.largeWidth) largeWidth')
            ->addSelect('ANY_VALUE(i.largeHeight) largeHeight')
            ->addSelect('ANY_VALUE(i.mediumWidth) mediumWidth')
            ->addSelect('ANY_VALUE(i.mediumHeight) mediumHeight')
            ->addSelect('ANY_VALUE(i.smallWidth) smallWidth')
            ->addSelect('ANY_VALUE(i.smallHeight) smallHeight')
            ->addSelect('ANY_VALUE(i.title) title')
            ->addSelect('ANY_VALUE(i.alt) alt')
            ->addSelect('ANY_VALUE(p.url) productUrl')
            ->join('o.products', 'p')
            ->join(Value::class, 'v')
            ->join('v.products', 'vp', Join::WITH, $qb->expr()->eq('vp.id', 'p.id'))
            ->join(Product::class, 'p0')
            ->join('p0.marketedName', 'mn0', Join::WITH, $qb->expr()->eq('mn0.alias', ':mnAlias'))
            ->join(Value::class, 'v0')
            ->join('v0.products', 'v0p', Join::WITH, $qb->expr()->eq('v0p.id', 'p0.id'))
            ->join('v0.attribute', 'v0a', Join::WITH, $qb->expr()->eq('v0a.alias', ':attribute'))
            ->leftJoin('p.price', 'pp', Join::WITH, $qb->expr()->gt('pp.current', 0))
            ->leftJoin('p.images', 'i', Join::WITH, $qb->expr()->eq('i.main', true))
            ->andWhere($qb->expr()->neq('p.id', 'p0.id'))
            ->andWhere($qb->expr()->neq('o.id', 'p0.marketedName'))
            ->andWhere($qb->expr()->eq('p.active', true))
            ->andWhere($qb->expr()->eq('v.attribute', 'v0.attribute'))
            ->andWhere($qb->expr()->eq('v.stringValue', 'v0.stringValue'))
            ->setParameter('attribute', 'atc')
            ->setParameter('mnAlias', $marketedName)
            ->groupBy('o.id');
    }

在函数参数中，我将已经收到：

QueryBuilder $qb -

SELECT m0_.id AS id_0, m0_.alias AS alias_1, m0_.url AS url_2, m0_.author AS author_3, m0_.censor AS censor_4, m0_.type AS type_5, m0_.brand AS brand_6, m0_.created_at AS created_at_7, m0_.updated_at AS updated_at_8 FROM marketed_name m0_

我试图添加到-from（$subquery），但它不工作，一个错误。和->setHint只在查询中可用，而不是QueryBuilder
我如何在这个函数中添加一个$subquery或hint而不破坏它，因为在这个函数之后仍然有处理作为QueryBuilder的参数？代码不是我写的，但我需要解决它。Symfony 4.4