SQL优化

将其更改为联接。MySQL在in()子句中没有很好地优化子查询-它是按行重新计算的(对于许多行来说效率非常低)

SELECT e.attr_id, e.sku, a.value
FROM product_attr AS e
INNER JOIN product_attr_text AS a ON e.attr_id = a.attr_id
INNER JOIN (SELECT value
    FROM product_attr_text 
    INNER JOIN eav_attr ON eav_attr.attribute_id=product_attr_text.attribute_id
    WHERE attribute_code = 'similar_prod_id'  
    AND value != ''
    GROUP BY value
    HAVING COUNT( value ) > 1 
) AS filter ON filter.value=a.value

转换查询后(可能需要根据架构进行一些更正),运行 EXPLAIN

SELECT  e.attr_id, e.sku, a.value
FROM    (
        SELECT  pat.value
        FROM    eav_attr ea
        JOIN    product_attr_text pat
        ON      pat.attribute_id = ea.attribute_id
        WHERE   ea.attribute_code = 'similar_prod_id'
                AND value <> ''
        GROUP BY
                value
        HAVING  COUNT(*) > 1
        ) q
JOIN    product_attr_text AS a
ON      a.value = q.value
JOIN    product_attr AS e
ON      e.attr_id  = a.attr_id

创建索引:

 eav_attr (attribute_code)
 product_attr_text (attribute_id, value)
 product_attr_text (value)
 product_attr (attr_id)

我只能说:

• 看看执行计划

• 使用IO统计信息-

其次,我不明白你为什么要用一个GROUPBY子句。。。

将第二个内部选择更改为联接:

SELECT e.attr_id, e.sku, a.value
FROM product_attr AS e, product_attr_text AS a
WHERE e.attr_id = a.attr_id
AND value
IN (
    SELECT at.value
    FROM product_attr_text at, eav_attr eat 
    WHERE at.attribute_id = eat.attribute_id 
          AND eat.attribute_code = 'similar_prod_id' 
          AND value != ''
    GROUP BY value
    HAVING (COUNT( value ) > 1 )
)

然后,查看执行计划以了解如何解决查询。您可能需要基于该执行计划进行额外的优化(例如,确保属性id字段已编制索引)。

弗雷德里克的回答是对的,但我想提出几点建议:

• 避免在,使用存在
• avoid=(从sometable中选择一些内容),改为use exists
• 在from子句中使用内部联接而不是多个表

但是,这又取决于你的机器/dbms/版本等等。。。为了获得最佳性能,您必须比较不同的执行计划

小心使用EAV(实体、属性、值)表设计的设计。由于引用的完整性和查询的复杂性,它们往往变得很难处理。

通过使用优化:

• 联接而不是子选择。
• 连接符号。
• 所有列名上的显式表别名。

SELECT e.attr_id, e.sku, a.value
  FROM product_attr AS e JOIN product_attr_text AS a
       ON e.attr_id = a.attr_id
 WHERE a.value IN (
          SELECT p.value
            FROM product_attr_text AS p
            JOIN eav_attr AS v ON p.attribute_id = v.attribute_id
           WHERE v.attribute_code = 'similar_prod_id'
             AND p.value != ''
           GROUP BY value
          HAVING (COUNT( value ) > 1)
          )

第二阶段:

SELECT e.attr_id, e.sku, a.value
  FROM product_attr AS e
  JOIN product_attr_text AS a ON e.attr_id = a.attr_id
  JOIN (SELECT p.value
          FROM product_attr_text AS p
          JOIN eav_attr AS v ON p.attribute_id = v.attribute_id
         WHERE v.attribute_code = 'similar_prod_id'
           AND p.value != ''
         GROUP BY value
        HAVING (COUNT( value ) > 1)
       ) AS x ON x.value = a.value

为组使用派生表,方法是。。。把它放回你需要的任何一张桌子上