delete inner join 慢的死机
-
SQL code
delete table1,table2 from table1 inner join table2 where table1.id = table2.id and table1.type = 'music'
2个表各储存一些信息,由相同的ID关联。
每个表105,000条记录,现在要删除table1.type = ‘music’的所有信息,大概7,000条。
我用上面的语句,结果造成死机。是不是1000个mysql connection都被消耗完了?
有什么方法提高效率吗?谢谢。
join要跟on来限定join的条件.
-
SQL code
delete table1,table2 from table1 inner join table2 on table1.id = table2.id where table1.type = 'music'
与连接数没关系。那1000个连接是指用户连接。
你的这个SQL语句慢可能的原因,
1) 查询速度慢,你的连接查询后产生大量记录,或者表中没有适当的索引。
2)其它并发用户锁定了表,造成了你的等待。
在mysql中inner join完全等同于,
好汉可以试试
-
SQL code
delete table1,table2 from table1 ,table2 where table1.id = table2.id and table1.type = 'music'
检查一下索引情况,在ID上有索引?
探讨
delete table1,table2
from table1
inner join table2
where table1.id = table2.id
and table1.type = 'music'
-
SQL code
create table delete_id as select table1.id from table1 inner join table2 on table1.id = table2.id where table1.type = 'music'; create index indx1 on delete_id(id); delete from table1 where id in(select id from delete_id); delete from table2 where id in(select id from delete_id);
-
SQL code
truncate table delete_id; drop table delete_id;