如何使用连接删除mysql行

Question

c2.execute("delete from entity_map where canon_id in (select canon_id from
entity_map_session where canon_id in (select canon_id from 
entity_map_session group by canon_id having count(canon_id) < 10))")

我想加快上述操作的速度。特别是我们删除行的最后一个。最好的方法是什么

Answer 1

DROP TABLE IF EXISTS table1, table2;

Answer 2

下一步应为“从表中删除”“使用联接”：

delete entity_map2.*
from entity_map2 
join (
    select canon_id 
    from entity_map_session2 
    group by canon_id having count(canon_id) < 10
) a on a.canon_id = entity_map2.canon_id;

Answer 3

针对entity_map_session2表的子查询正在进行计数聚合，并且没有WHERE或HAVING子句，这意味着无法对其进行太多优化。但是，您可以删除其中一个不必要的嵌套子查询，例如使用：

DELETE
FROM entity_map2
WHERE canon_id IN (SELECT canon_id FROM entity_map_session2
                   GROUP BY canon_id HAVING COUNT(canon_id) < 10);