query optimization with case statement

Question

I have a column with codes. Now each code has been changed to something else. I am trying to update it. So I have used case statement in mysql. But the problem is, I have around 250,000 rows and 80,000 unique codes which need to be replaced. And the case statement is taking like 10 min to execute. Any better approach to do this.

My query looks like this:

UPDATE test_table
SET code = CASE
WHEN code = "akdsfj" THEN "kadjsf"
WHEN code = "asdf" THEN "ndgs"
WHEN code = "hfgsd" THEN "gfdsd"
... (I am doing in batches of 1000 case statements at a time)
ELSE code

Gordon Linoff · Accepted Answer · 2016-09-14T23:48:32.433

The case statement does add time, because it is searched.

The solution? Store the pairs in a temporary table . . . with an index. So:

create temporary table code_pairs (
    old_code varchar(255) not null primary key,
    new_code varchar(255)
);

insert into code_pairs(old_code, new_code)
    values ('akdsfj', 'kadjsf'),
           ('asdf', 'ndgs'),
           . . . ;

Then use update with join:

update test_table tt join
       code_paris cp
       on tt.code = cp.old_code
    set tt.code = cp.new_code;

This saves you time because the matching code is found using the index, rather then searching one-by-one through a case statement. In addition, no update is attempted on rows that have no match. The 170,000 rows with no match are probably the slowest part of the query, because they need to go through the entire list of case values.

query optimization with case statement

1 Answers1

Linked