mysql优化问题

Question

猜猜为什么这个语句在处理 300 000 行时花费的时间太长。基本上这个查询是为了查找重复项。

SELECT DISTINCT 
    a.Id,
    b.Id as sid
FROM 
    csv_temp a
INNER JOIN 
    csv_temp b ON a.firstname = b.firstname AND 
    a.lastname = b.lastname  AND 
   ((a.address = b.address) OR 
    (a.zip = b.zip) OR 
    (a.city = b.city AND a.state = b.state) )
WHERE 
    a.Id <> b.Id AND 
    a.status=2 AND 
    b.status=1 AND 
    a.flag !=1 AND 
    b.flag !=1

Answer 1

OR 的性能似乎通常很差，在 JOIN 条件下，我预计情况会更糟。尝试使用 3 个 SELECT(一个用于每个 ORed 条件)并将结果结合在一起。如果这样做，怀疑也不需要 DISTINCTS:-

SELECT  
    a.Id,
    b.Id as sid
FROM 
    csv_temp a
INNER JOIN 
    csv_temp b ON a.firstname = b.firstname AND 
    a.lastname = b.lastname  AND 
   a.address = b.address
WHERE 
    a.Id <> b.Id AND 
    a.status=2 AND 
    b.status=1 AND 
    a.flag !=1 AND 
    b.flag !=1
UNION
SELECT  
    a.Id,
    b.Id as sid
FROM 
    csv_temp a
INNER JOIN 
    csv_temp b ON a.firstname = b.firstname AND 
    a.lastname = b.lastname  AND 
    a.zip = b.zip
WHERE 
    a.Id <> b.Id AND 
    a.status=2 AND 
    b.status=1 AND 
    a.flag !=1 AND 
    b.flag !=1
UNION
SELECT  
    a.Id,
    b.Id as sid
FROM 
    csv_temp a
INNER JOIN 
    csv_temp b ON a.firstname = b.firstname AND 
    a.lastname = b.lastname  AND 
    a.city = b.city AND a.state = b.state
WHERE 
    a.Id <> b.Id AND 
    a.status=2 AND 
    b.status=1 AND 
    a.flag !=1 AND 
    b.flag !=1