How to create a stored procedure with several delete in(select) statements?

Question

Our database needs to be periodically cleared of certain items that receive a specific flag (A4) on their id. Our application manages stores and all tables are myISAM. The items can be either in the bought, sold, exchanged, etc tables and they will also exist in the main table that contains all items (the only information the tables share is the id and I can't control that at the moment).

My initial idea was to create the individual delete statements and then place them together in a stored procedure. The stored procedure I created contains:

delimiter $$

create procedure clear_A4(in period char(10))

BEGIN

delete from mov_pedido where pednot in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_orcame where orcnot in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_nfsven where nfsnum in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_movime where movdoc in (select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_titulo where titnum like 'A4%' and titemi<='2011-12-01' and titvalpag >0;

end $$

delimiter ;

These lines refer to items sold. A sale starts with a order, then a quotation, then the actual sale, and finally it gets throw in the main record. The first table mov_pedido has the orders, mov_orcame has the quotations, mov_nfsven has the actual sales and mov_movime records all the transactions. These tables can only be cleared of items that have been paid for by customers but this information can only be found on mov_titulo so I decide to use the select statements with the IN operator. The problem is I also need to clear the mov_titulo table. If I delete the items from mov_titulo first then the information used in the select statements is lost.

Reading up on mySQL,multithreaded databases it seems that if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

My question is: how can I improve the logic of my delete statement to make them work together? Can I improve my stored procedure to deal with this?

Right now I am executing one statement at a time manually, and that works, but I can see problems down the line, such as entering the wrong date in one of the existing 20 some delete statements for example so I am looking for a better way to do this.

Thank you.

Answer 1

if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

No, the statements in a stored procedure are processed sequentially, not in parallel. A stored procedure executes in a single thread, just as if you had issued the queries one after another... not simultaneously.

There are some optimizations you could make, however.

If the `titnum` table doesn't have a multi-column index on the three columns (titvalpag, titemi, titnum) together, you should probably add one.

The delete statements can be rewritten without the IN().

Use caution with this, because doing it incorrectly will remove the wrong data.

delete from mov_pedido 
where pednot in(select distinct titnum 
                  from mov_titulo 
                 where titnum like 'A4%' 
                   and titemi <='2012-01-01' and titvalpag >0);

...becomes...

DELETE p
  FROM mov_pedido p
  JOIN mov_titulo m ON m.titnum = p.pednot
 WHERE m.titnum like 'A4%' and m.titemi <='2012-01-01' and m.titvalpag >0;

This is the multi-table delete syntax. "DELETE p" will only remove the rows from "p" (an alias for mov_pedido) that match rows in "m" (mov_titulo). It won't remove the rows from "m" unless you say "DELETE p, m" -- so don't do that.

The mov_pedido table (and the others) should have an index on the column you're using to join (pednot, etc.) if they don't already.

Answer 2

You can also insert a SLEEP command between the deletes...

SELECT SLEEP(<seconds>);

Now it may cycle back through some of the original deletes, but since they have already ran, it should just go to the next one...