I have the following delete SQL statement:

delete w,r from table1 w, table2 r where (w.status=1 or r.status=1)

show engine innodb status shows the following. is the read rate(237.29 reads/s) referring to the reads after the filter(where) condition? or is it referring to all elements of the join prior to the filter? This is importance since I'm trying to gauge the time it would take to finish the query. Both tables are very large however the filtered results are relatively small.

1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 5163, id 1090357568, state: waiting for server activity
Number of rows inserted 25005429, updated 1722842, deleted 371088, read 2176564571
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 237.29 reads/s

Am I wrong or you did not specify any join condition on w and r? That mean your are doing a big cartesian join of both tables, this would be certainly longer than 2 delete queries, one on each table.

Don't you have something that make w and r relatd to each other, like:

delete w,r 
from table1 w ,table2 r 
where (w.r_id=r.r_id) 
and (w.status=1 or r.status=1)

or at least you could join them on the status field. And obviously indexing the status field is a must.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.