我有两个表,A表的ID,可能是B表中的三个字段ID0,ID1,ID2中的一个。
由于在对B表的这三个字段做更新操作的时候(更新IDO或ID1或ID2为0),没有删除A表中相应ID的记录。现在我想把在B表中有对应ID的记录全选出来,但是我下面写的语句效率太低了。5000条记录要等一两个小时。各位帮我看看能不能优化一下,我对MYSQL不是很熟
SELECT * FROM A WHERE ID IN (SELECT ID0 FROM B UNION ALL SELECT ID1 FROM B UNION ALL SELECT ID2 FROM B)
由于在对B表的这三个字段做更新操作的时候(更新IDO或ID1或ID2为0),没有删除A表中相应ID的记录。现在我想把在B表中有对应ID的记录全选出来,但是我下面写的语句效率太低了。5000条记录要等一两个小时。各位帮我看看能不能优化一下,我对MYSQL不是很熟
SELECT * FROM A WHERE ID IN (SELECT ID0 FROM B UNION ALL SELECT ID1 FROM B UNION ALL SELECT ID2 FROM B)
SELECT * FROM A inner join
(SELECT ID0 FROM B UNION ALL SELECT ID1 FROM B UNION ALL SELECT ID2 FROM B) b1
on a.id=b1.id0
SELECT * FROM A inner join
(select * from (
(SELECT ID0 FROM B UNION ALL SELECT ID1 FROM B UNION ALL SELECT ID2 FROM B) ) b1
on a.id=b1.id0
SELECT * FROM A inner join
(select * from (
SELECT ID0 FROM B UNION ALL SELECT ID1 FROM B UNION ALL SELECT ID2 FROM B) ) b1
on a.id=b1.id0上面多了一个(
from A
where exists (select * from B where id0=a.id or id1=a.id or id2=a.id)如果B表的数据量不太,这个应该会比较快。[align=center]==== 思想重于技巧 ====
[/align]