MySQL Query running slow when using REPLACE function instead of CASE statement

MySQL

Is it better to use CASE statements instead of REPLACE functions in MySQL when mapping a comma-separated-string field?

The below query runs extremely slow using replace.
Note, the underlying user_roles table is of format [user_id (bigint), string_of_user_role_ids (varchar(200))]

-- this runs slowly
select      string_of_user_role_ids
            , replace(replace(replace(replace(replace(string_of_user_role_ids,
    '10', 'Scientist'), '9', 'Superhero'), '8', 'Teacher'), '7', 
         'Journalist'), '6', 'Farmer')
            , count(1) 
from        user_roles
group by    1,2 
order by    3 desc

-- this runs quickly, but is more difficult to keep adding in multiple new when clauses whenever a new user role is added
select      string_of_user_role_ids
            , case  when string_of_user_role_ids= "6" then 'Farmer'
                    when string_of_user_role_ids= "7" then 'Journalist'
                    when string_of_user_role_ids= "8" then 'Teacher'    
                    when string_of_user_role_ids= "6,7" then 'Farmer, Journalist'
                    when string_of_user_role_ids= "6,8" then 'Farmer, Teacher'
                    when string_of_user_role_ids= "7,8" then 'Journalist, Teacher'
                    when string_of_user_role_ids= "6,7,8" then 'Farmer, Journalist, Teacher'    
                    -- ... etc.
                    else 'Unknown' end as app_user_type
            , count(1) 
from        user_roles  
group by    1,2 
order by    3 desc

Ideally I would use the REPLACE function instead of a CASE statement, as it seems easier to scale out in terms of expanding the code and less risk to manage.

I can't understand why one query runs quickly and the other very slowly (seconds versus minutes, after a few mins I killed the slow query).
Ideas/questions are welcome please.

Explain statement output for both is the same:
Explain statement output

Best Answer

It would work faster if you would use replace/case after group by. For example:

select      string_of_user_role_ids
            , replace(replace(replace(replace(replace(string_of_user_role_ids,
    '10', 'Scientist'), '9', 'Superhero'), '8', 'Teacher'), '7', 
         'Journalist'), '6', 'Farmer')
            , cnt 
from        (select      string_of_user_role_ids
                        , count(1) as cnt
            from        user_roles
            group by    string_of_user_role_ids 
            ) as ur
order by    cnt desc
Related Question