避免子查询以查找列中具有相同值的所有行

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+ secs运行。