查询优化

Question

我在一个表中有50964218条记录。我将从这个表中获取数据并插入到同一个表中。这需要更多的时间来操纵。如何优化此查询。

查询为

INSERT INTO contacts_lists (contact_id, list_id, is_excluded, added_by_search)
SELECT contact_id, 68114 , TRUE, added_by_search
FROM contacts_lists cl1
WHERE list_id = 67579
AND is_excluded = TRUE
AND NOT EXISTS 
    (SELECT 1 FROM contacts_lists cl2 
     WHERE cl1.contact_id = cl2.contact_id AND cl2.list_id = 68114 )

索引: list_id，contact_id

Answer 1

使用left join可能会得到更好的结果：

select t1.[field], ...
from t1
left join t2
on [conditions]
where t2.[any pkey field] is null;