Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the following procedure:

drop procedure  if exists deletebatches;

create procedure deletebatches()

declare acount int default 0;
declare done INT default 0;
declare abatchno varchar(10);
declare abatchpart varchar(20);
declare cursor1 cursor   for select batchpart, batchno from batchestodelete;
open cursor1;
readloop: loop
fetch cursor1 into abatchpart, abatchno;
if done = 1 then leave readloop;
end if;

delete from stockbatches where batch_batch = abatchno
and batch_part = abatchpart;

select curtime();
end loop readloop;

close cursor1;


call deletebatches()

This query runs for so long that I have not had the patience to let it finish. I cancel it after an hour or so.

I also created a query to delete from stockbatches where batch_batch + batch_part in (select batch_batch + batch_part from batchestodelete) and it took 1,278 seconds (over 21 minutes!).

There are only 776 records in the batchestodelete table and about 1 million in the stockbatches table which has indexes on batch_part, batch_batch and both fields combined.

Can anybody offer an explanation as to why this is taking so long for what is a relatively simple query?

Here are some pieces of information various people have asked from me.

Show full process list shows:

2614 root localhost:4785 Sleep 4786
2621 root localhost:4792 new_web_files Sleep 4789
2967 root localhost:1283 new_web_files Query 3 updating 
     delete from stockbatches where batch_batch = abatchno 
     and batch_part = abatchpart 3014 root localhost:1336 new_web_files 
     Query 0 show full processlist

Show create table stockbatches shows:

    batch_part varchar(20) collate latin1_general_ci NOT NULL, 
    batch_batch varchar(10) collate latin1_general_ci NOT NULL, 
    batch_qty decimal(18,2) default NULL, 
    batch_cond varchar(2) collate latin1_general_ci default NULL, 
    batch_release varchar(3) collate latin1_general_ci default NULL, 
    batch_curedate varchar(10) collate latin1_general_ci default NULL, 
    batch_lastmod datetime default NULL, 
    batch_lastaction varchar(1) collate latin1_general_ci default NULL, 
    PRIMARY KEY (batch_part,batch_batch), 
    KEY partno (batch_part), 
    KEY batchno (batch_batch), 
    KEY batchandpart (batch_part,batch_batch) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Show create table batchestodelete shows:

batchestodelete CREATE TABLE batchestodelete ( 
    batchpart varchar(20) NOT NULL, 
    batchno varchar(10) character set latin1 collate latin1_general_ci default NULL 
share|improve this question
Welcome to DBA stackexchange, Ron. Can you provide the SHOW FULL PROCESSLIST while the stored proc is running? Also, can you give SHOW CREATE TABLE for both tables involved? – Derek Downey Feb 21 '12 at 16:55
Also, not part of a performance issue, but I notice you are doing SET DONE = 0;, but everywhere else it is 'done'. – Derek Downey Feb 21 '12 at 16:57
I have just seen your later post with the delete with join. It worked brilliantly in 5 seconds. Thank you for your help, I am learning all the time. :) – user6880 Feb 22 '12 at 7:29
Would it be possible to utilise the code you gave me from an MSSQL query to a linked MySql server such as this? DELETE FROM OPENQUERY(ronsmysql, 'SELECT * FROM stockbatches') WHERE batch_part = @batchpart and batch_batch = @batchno but using your version of the code. I cannot get my head around the syntax for the openquery. – user6885 Feb 22 '12 at 11:24

Why are you using a cursor for this type of operation?

Most RDBMS are optimised for set-based operation, a cursor or a loop are iterative operations which perform row-by-row operations.

Whenever possible, you want to use declarative set-based code.

Try this:

FROM stockbatches sb
INNER JOIN batchestodelete btd
ON sb.batch_batch = btd.batchno
AND sb.batch_part = btd.batchpart
share|improve this answer
Very good points, Vince, and I do prefer the set operation over a cursor. +1 – Derek Downey Feb 21 '12 at 17:23
The (first line) DELETE FROM stockbatches should be: DELETE sb – ypercubeᵀᴹ Feb 21 '12 at 22:12
+1 for coming up with the DELETE JOIN concept first – RolandoMySQLDBA Feb 21 '12 at 22:26

Just a stab in the dark at the moment, but this query throws a red flag to me:

delete from stockbatches where batch_batch = abatchno and batch_part = abatchpart;

If abatchno and/or abatchpart are ever empty (such as might be the case on the last iteration of the loop prior to done=1), your query would end up looking something like this:

DELETE FROM stockbatches WHERE batch_batch AND batch_part;

Which would likely delete a lot more than you are intending.

share|improve this answer

Your Answer


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.