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 distinct 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 distinct 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.