vendredi 27 mars 2015

delete query performance issue on mysql


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, Iam 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 "order by" clause in the above query its showing performance improvement but i get below warning;


Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.


Please suggest a way improve the performance of the delete query.





Aucun commentaire:

Enregistrer un commentaire