MySql hangs on update and insert -

MySql hangs on update and insert -

i have e-commerce site, many old products never used.

i wrote code find these products , move them table.

the code finds products should deleted , mark them column should_delete=1;

here code re-create product (count = number of product delete):

while ($count>0) { if ( $db->execute("insert delete_product select p.* product p p.should_delete ='1' limit 500" )){ $db->execute("update product p set p.should_delete='2' p.id_product in (select `id_product` deleted_products)"); } $count-= 500; sleep(1); }

at first runs fast, when 'show processlist' see query take 1 sec.

but become slow, , queries take 1 hour.

i'm running on qa srv not in utilize others.

i have lots of free disk space (3.5g)

there 80308 products in db.

and 29511 mark deletion.

the db in innodb

it's running hours 6500 copied.

the 'show processlist' show 'sending data' state

what missing? why these simple queries slow?

i able improve sec query -

update product p bring together deleted_products dp on p.id_product = dp.id_product set p.should_delete='2' p.should_delete='1'

but insert still hangs.

here version of same code hangs -

while ($count>0) { $fname = microtime(true); $db->execute("select * product p p.should_delete='1' limit 500 outfile '/tmp/".$fname.".txt'"); if ( $db->execute("load info infile '/tmp/".$fname.".txt' table deleted_products" )){ $db->execute("update product p bring together deleted_products dp on p.id_product = dp.id_product set p.should_delete='2' p.$should_delete='1'"); } $count-= 500; sleep(1); }

this version hangs on -

58 | root | localhost | prestashop2 | query | 29192 | null | load info infile '/tmp/1414615714.6019.txt' table deleted_products

it looks deleted_products somehow locks, new table i've created , no else in code it's reference, , no 1 else using srv.

your deleted_products table increasing every time loop going through. when time goes on part of query increasing:

p.id_product in (select `id_product` deleted_products)"

so imagine starts 500 gets 1000,1500,... till it's slow. hence suggestion add together status following.

&anawesomenewvariable=501; while ($count>0) { if ( $db->execute("insert delete_product select p.* product p p.should_delete . ='1' limit 500" )) { $db->execute("update product p set p.should_delete='2' p.id_product in (select `id_product` deleted_products id_product<&anawesomenewvariable , id_product >&anawesomenewvariable-500)"); } $count-= 500; &anawesomenewvariable+=500; sleep(1); }

mysql