By no means a criticism, but meant only as something to consider ...
On certain mysql patches, there is a need to iterate through every record in a table and update. For example in 0422~releases.sql there is a need to recategorize. Here is the relevant mysql snippet:
#Change categoryids of existing releases to new values.
UPDATE releases SET categoryid = 0010 WHERE categoryid = 7010;
UPDATE releases SET categoryid = 0020 WHERE categoryid = 7020;
UPDATE releases SET categoryid = 7010 WHERE categoryid = 8010;
UPDATE releases SET categoryid = 7020 WHERE categoryid = 8020;
UPDATE releases SET categoryid = 7030 WHERE categoryid = 8030;
UPDATE releases SET categoryid = 7040 WHERE categoryid = 8040;
UPDATE releases SET categoryid = 7050 WHERE categoryid = 8050;
UPDATE releases SET categoryid = 7060 WHERE categoryid = 8060;
This code is certainly clean, neat, professional, etc... but on a large collection the commands cause mysql to loop through the table for each update command.
In such an instance, this update can be rewritten as:
UPDATE releases SET categoryid =
CASE
WHEN 7010 THEN 0010
WHEN 7020 THEN 0020
WHEN 8010 THEN 7010
...
END
resulting in a major improvement in the update process as each record is only visited once.
On large collections this will be a difference of many hours. I thought I'd put this up here in the forums for sort of a template as the logic is easy to follow.
Rgrds,
nzbmets