MySQL 避免使用子查询来查找列中具有相同值的所有行

Question

我尝试执行以下查询来查找特定列中具有相同值的其他 5 行的行:

SELECT * FROM `tbl` WHERE columnName IN (
   SELECT columnName FROM `tbl` GROUP BY columnName HAVING COUNT(*) > 5
)

该查询似乎需要很长时间，我需要手动终止它。然而，子查询本身非常快。我想要整行的原因是因为我计划删除这些行。我知道子查询有时会变得非常慢，最好避免它们。

如何在不使用子查询的情况下编写相同的内容？

Answer 1

您可以使用临时表作为快速执行的方法:

-- creating indexed temporary results

DROP TEMPORARY TABLE IF EXISTS `tmp_tbl`;

CREATE TEMPORARY TABLE `tmp_tbl`
   SELECT columnName FROM `tbl` GROUP BY columnName HAVING COUNT(*) > 5;

CREATE INDEX icolname ON  `tmp_tbl`(columnName);

-- now selecting your data
SELECT `tbl`.* 
FROM `tbl` INNER JOIN `tmp_tbl` on `tbl`.columnName = `tmp_tbl`.columnName;

-- to delete your data
DELETE FROM `tbl` 
INNER JOIN `tmp_tbl` on `tbl`.columnName = `tmp_tbl`.columnName;

我在一个包含 100000 多条记录的表中进行了测试，它在 2-4 秒内运行，而使用子查询则需要 50 多秒。