Slow performance on updates - weird

[Daniel Constantinov]

Hello fellows,

maybe this is silly (or weird) question but I have experiencing very slow updates on a very simple situation. Let me explain:

Simplifying, my table (prod) have about 400K records with two fields involved (id_prc and id_ccentr).

If I do one simple update like this: "Update prod set id_prc=1;", it takes about 11 seconds for the whole table.

But when I do many updates based on id_ccentr the initial ones goes very fast, but after a while the updates slows down for very seconds, and after, goes faster one more time, and slow again...
Example:
Update prod set id_prc=1 where id_ccentr=10;
Update prod set id_prc=2 where id_ccentr=30;
Update prod set id_prc=3 where id_ccentr=56;
etc, etc, until all rows are changed based on id_ccentr ... To the finish this process takes about 190 seconds (against 11 on the first case).

Well, I try to put LOCKS around the updates but no gain, and I have tested to drop the index that exists on id_prc and then the updates goes more faster, but, the task of drop and create index consumes many seconds and that are no gain involved.

I have a MyISAM table on MySQL 5.077 running on Linux (Centos 5.3) box with 4Gb of RAM running mainly MySQL. I repeat this test many many times with similar results.

If anyone could help me please let me know what more parameters do you need.

Thanks in advance