Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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