Very slow performance from MySQL I have the following procedure:

drop procedure  if exists deletebatches;

create procedure deletebatches()

BEGIN
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;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
SET DONE = 0;
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;

end;

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:

STOCKBATCHES CREATE TABLE stockbatches ( 
    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 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
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:

DELETE sb
FROM stockbatches sb
INNER JOIN batchestodelete btd
ON sb.batch_batch = btd.batchno
AND sb.batch_part = btd.batchpart