nZEDb
nZEDb => Development => Topic started by: NZBmets on 20160126, 06:00:41 am

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

NZBMets, I've updated patch 422 inline with your suggestion. Would you take a look at it please, to be sure it is what you meant/had in mind?

It seems the shorthand version didn't have the desired outcome. It may need to be explicit and use an ELSE.
#Change categoryids of existing releases to new values.
UPDATE releases SET categoryid =
(
CASE
WHEN categoryid = 7010 THEN 0010
WHEN categoryid = 7020 THEN 0020
WHEN categoryid = 8010 THEN 7010
WHEN categoryid = 8020 THEN 7020
WHEN categoryid = 8030 THEN 7030
WHEN categoryid = 8040 THEN 7040
WHEN categoryid = 8050 THEN 7050
WHEN categoryid = 8060 THEN 7060
ELSE categoryid = categoryid
END
);