[已解决]mysql执行搜索语句很慢是什么原因

937135952

自己用爬虫建立的一个一万条数据的数据库，想要删除重复项。

1. DELETE FROM newtable WHERE id IN (
   
2.     SELECT * FROM (
   
3.         SELECT id FROM newtable WHERE (job_name,company_href,issuedate)
   
4.         IN (
   
5.             SELECT job_name,company_href,issuedate FROM newtable GROUP BY job_name,company_href,issuedate HAVING COUNT(1) > 1
   
6.         ) AND id NOT IN (
   
7.             SELECT MIN(id) FROM newtable GROUP BY job_name,company_href,issuedate HAVING COUNT(1) > 1
   
8.         )
   
9.     ) AS newtable_copy
   
10. 
    
11. );

复制代码

用如上代码，执行超时

1.     SELECT * FROM (
   
2.         SELECT id FROM newtable WHERE (job_name,company_href,issuedate)
   
3.         IN (
   
4.             SELECT job_name,company_href,issuedate FROM newtable GROUP BY job_name,company_href,issuedate HAVING COUNT(1) > 1
   
5.         ) AND id NOT IN (
   
6.             SELECT MIN(id) FROM newtable GROUP BY job_name,company_href,issuedate HAVING COUNT(1) > 1
   
7.         )
   
8.     ) AS newtable_copy

复制代码

执行该代码，花费了15分钟，请教一下是什么原因为什么需要这么久？

最佳答案

月排行榜 / 总排行榜

kogawananari

2022-1-29 10:50:47

NOT IN  改成 EXISTS

跳转到最佳答案楼层

kogawananari

NOT IN  改成 EXISTS