1

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 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.

Community
  • 1
user3467431
  • 11
  • 1
  • What transaction isolation level are you using? – BuahahaXD Mar 27 '15 at 10:05
  • Do you have any indexes on the 2 tables? Add the output of SHOW CREATE TABLE ...; for both. – ypercubeᵀᴹ Mar 27 '15 at 14:49
  • Deleting the 10 first records (order by limit 10) does not make a lot of sense. Essentially you are deleting 10 random records (except that they are ordered). So the warning from MySQL is correct except that they should also give you the warning when limit is used in any scenario. What is your goal in the deletion? Can you not use another where clause getting you better performance? – Norbert Mar 27 '15 at 20:59

0 Answers0