Author Topic: SQL Patches - Linear updates (e.g. 0422~releases.sql)  (Read 1786 times)

Offline NZBmets

  • Prolific Indexer
  • ****
  • Posts: 260
  • Helpful: +11/-0
SQL Patches - Linear updates (e.g. 0422~releases.sql)
« on: 2016-01-26, 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:

Code: [Select]
#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:
Code: [Select]
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
« Last Edit: 2016-01-26, 06:11:50 AM by NZBmets »

Offline archer

  • Overlord
  • ******
  • Posts: 25
  • Helpful: +5/-0
Re: SQL Patches - Linear updates (e.g. 0422~releases.sql)
« Reply #1 on: 2016-01-26, 01:10:47 PM »
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?
Support my tech addiction.

Offline NZBmets

  • Prolific Indexer
  • ****
  • Posts: 260
  • Helpful: +11/-0
Re: SQL Patches - Linear updates (e.g. 0422~releases.sql)
« Reply #2 on: 2016-01-26, 01:45:04 PM »
It seems the shorthand version didn't have the desired outcome. It may need to be explicit and use an ELSE.

Code: [Select]
#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
);
« Last Edit: 2016-01-27, 04:23:56 AM by NZBmets »