MYSQL delete optimization

Question

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.

ROW OPERATIONS
--------------
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

Answer 1

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.