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_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