mysql - mysql中查找重复项

Question

SELECT COUNT(organization.ID)
FROM organization
WHERE organization.NAME IN (
    SELECT organization.NAME
    FROM organization
    WHERE organization.NAME <> ''
        AND organization.APPROVED = 0 
        AND organization.CREATED_AT > '2012-07-31 04:31:08'
    GROUP BY organization.NAME
    HAVING COUNT(organization.ID) > 1
)

此查询发现重复项，问题是由于内部语句，页面加载需要 6 秒。有没有办法让它跑得更快？ MySQL 数据库版本 5.1。

Answer 1

是的。这很慢，因为 MySQL 处理“in”查询的速度很慢。您可以使用以下方法来修复它:

SELECT COUNT(organization.ID)
FROM organization o
WHERE exists (
    SELECT organization.NAME
    FROM organization o2
    WHERE organization.NAME <> ''
        AND organization.APPROVED = 0 
        AND organization.CREATED_AT > '2012-07-31 04:31:08' and
        organization.name = o.organization.name
    GROUP BY organization.NAME
    HAVING COUNT(organization.ID) > 1
)