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

Question

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:

Answer 1

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

Answer 2

Instead of trying to parse a string column, use the database's built-in relational capabilities as they were intended to be used.

First, we need three tables, Users, Roles, and UserRoles. UserRoles is a cross reference table that joins Users to their Roles.

CREATE TABLE Users
(
    UserID int NOT NULL primary key
    , UserName varchar(100) NOT NULL
);

CREATE TABLE Roles
(
    RoleID int NOT NULL primary key
    , RoleName varchar(50) NOT NULL
);

CREATE TABLE UserRoles
(
    UserID int NOT NULL
    , RoleID int NOT NULL
    , FOREIGN KEY UserRolesUserIDFK (UserID) REFERENCES Users (UserID)
    , FOREIGN KEY UserRolesRoleIDFK (RoleID) REFERENCES Roles (RoleID)
    , PRIMARY KEY (UserID, RoleID)
);

In the UserRoles table above, you'll note we have two foreign keys, one each for the User entry and the associated Role entry. This provides referential integrity that prevents deletion of a role that is in use by a user, and adding a user to a non-existent role. I've also added a compound primary key to the table, on (UserID, RoleID), which means you cannot add a user to a single role more than once. This eliminates the need to DISTINCT the output queries below, which for a system with a large number of Users and Roles would make a significant positive difference to performance.

Next, we'll insert some sample data:

INSERT INTO Users (UserID, UserName)
VALUES (1, 'Sarah')
     , (2, 'Hannah');

INSERT INTO Roles (RoleID, RoleName)
VALUES (1, 'Administrator')
     , (2, 'User');

INSERT INTO UserRoles (UserID, RoleID)
VALUES (1, 2)
     , (2, 1)
     , (2, 2);

Now, when we want to get the results, we do this simple query:

SELECT u.UserName
    , r.RoleName
FROM Users u
    INNER JOIN UserRoles ur ON u.UserID = ur.UserID
    INNER JOIN Roles r ON ur.RoleID = r.RoleID
ORDER BY u.UserName;

Results look like:

╔══════════╦═══════════════╗
║ UserName ║   RoleName    ║
╠══════════╬═══════════════╣
║ Hannah   ║ Administrator ║
║ Hannah   ║ User          ║
║ Sarah    ║ User          ║
╚══════════╩═══════════════╝

If you need the role membership on a single line, along with the count of the number of roles each user is a member of, then the following should work:

SELECT u.UserName
    , (
        SELECT GROUP_CONCAT(r.RoleName SEPARATOR ', ') 
        FROM Roles r 
            INNER JOIN UserRoles ur ON r.RoleID = ur.RoleID
        WHERE ur.UserID = u.UserID) AS Roles
    , (
        SELECT COUNT(1)
        FROM UserRoles ur
        WHERE ur.UserID = u.UserID) AS CountOfRoles
FROM Users u
ORDER BY u.UserName;

Output looks like:

╔═══════════╦══════════════════════╦══════════════╗
║ UserName  ║        Roles         ║ CountOfRoles ║
╠═══════════╬══════════════════════╬══════════════╣
║ Hannah    ║ Administrator, User  ║            2 ║
║ Sarah     ║ User                 ║            1 ║
╚═══════════╩══════════════════════╩══════════════╝

The queries above can efficiently use indexes without any headaches, and they easier to comprehend than a a large number of REPLACE or CASE statements.

Multiple REPLACE or CASE statements, where not necessary, only introduce complexity that may mask the real intention, and confuse the query optimizer, producing poor plans and possibly slow results.

A significant benefit of using discrete rows for each user/role relationship is you can have an unlimited number of roles per user, without needing an unlimited size string column to hold the membership details. This also makes it super easy to determine if someone is a member of a specific role, or to see all the members of specific roles.

For example, to see who's a member of the User role, you could do this:

SELECT u.UserName
FROM Users u
    INNER JOIN UserRoles ur ON u.UserID = ur.UserID
    INNER JOIN Roles r ON ur.RoleID = r.RoleID
WHERE r.RoleName = 'User'
ORDER BY u.UserName;

Results:

╔══════════╗
║ UserName ║
╠══════════╣
║ Hannah   ║
║ Sarah    ║
╚══════════╝

This design also makes it easy to see who is not a member of a specific role:

SELECT u.UserName
FROM Users u
WHERE NOT EXISTS (
    SELECT 1
    FROM UserRoles ur
        INNER JOIN Roles r ON ur.RoleID = r.RoleID
    WHERE r.RoleName = 'Administrator'
        AND ur.UserID = u.UserID
    );

Results:

╔══════════╗
║ UserName ║
╠══════════╣
║ Sarah    ║
╚══════════╝

I've created a fiddle for the above code here.