I have two tables table1, table2 where table2 is a subset of table1 (basically created with the query create table table2 as select * from table1 where .....)
Now, I am trying to run the below query. But its taking almost 30 minutes to just delete 10 records.
delete from table1
where column1 in
(select column2 from table2)
order by column1
limit 10
If I remove the order by clause in the above query it's showing performance improvement but I get the warning;
Unsafe statement written to the binary log using statement format since
BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses aLIMITclause. This is unsafe because the set of rows included cannot be predicted.
Please suggest a way improve the performance of the delete query.