slow delete query
Posted on 2009-03-31
I'm finding this query takes about 50 minutes to run. Is there a way to optimize?
delete from A
where A.createdate IN (select B.createdate from B group by B.createdate);
I need to run this query daily. Basically, B is an daily set of rows that update Table A. So I remove any rows where the date already exists in B, then add everything in B to A. Table A has 7 million rows, but is growing at 400K rows or so a day. Table B has 2 million rows.
In both cases, "createdate" is a DATE field.