Currently I have the following two tables (shown here somewhat simplified):

users: (userId, username, email, name) permissions: (id, userId, permission)

The permission field may contain, for example, something like canRead, canWrite, canDelete, etc. Each user can have any number of permissions.

I have now written a query to display all users and to get the permissions of each user as an extra boolean column. For example, this is what a result of the SQL script should look like for two users registered in the system:

userId: 1, username: "testuser", email: "[email protected]", name: "testname", canRead: 1, canWrite: 0, canDelete: 0
userId: 2, username: "anotheruser", email: "[email protected]", name: "anothername", canRead: 1, canWrite: 1, canDelete: 1

My current query looks like this:

SELECT users.userId, users.username, users.email, users.name,
CASE WHEN(
  SELECT DISTINCT permissions.permission
  FROM permissions
  WHERE permissions.permission = 'canRead' AND users.userId = permissions.userId
) IS NULL THEN 0 ELSE 1 END AS 'canRead',
CASE WHEN(
  SELECT DISTINCT permissions.permission
  FROM permissions
  WHERE permissions.permission = 'canWrite' AND users.userId = permissions.userId
) IS NULL THEN 0 ELSE 1 END AS 'canWrite',
CASE WHEN(
  SELECT DISTINCT permissions.permission
  FROM permissions
  WHERE permissions.permission = 'canDelete' AND users.userId = permissions.userId
) IS NULL THEN 0 ELSE 1 END AS 'canDelete',
FROM users
LEFT JOIN permissions
ON permissions.userId = users.userId
GROUP BY users.userId

The more permissions I add the slower the query becomes. How can I write this query in a more simplified and especially performant way?

CodePudding user response：

You can done this by single JOIN and some data manipulation:

SELECT 
    users.userId, users.username, users.email, users.name,
    COUNT(IF(permissions.permission = 'canRead', 1, null)) > 0 'canRead',
    COUNT(IF(permissions.permission = 'canWrite', 1, null)) > 0 'canWrite',
    COUNT(IF(permissions.permission = 'canDelete', 1, null)) > 0 'canDelete'
FROM users
LEFT JOIN permissions ON users.userId = permissions.userId
GROUP BY users.userId, users.username, users.email, users.name;

MySQL JOIN fiddle

CodePudding user response：

Simplify it. Max 'em.

SELECT u.userId, u.username, u.email, u.name
, MAX(CASE WHEN p.permission = 'canRead' THEN 1 ELSE 0 END) AS canRead
, MAX(CASE WHEN p.permission = 'canWrite' THEN 1 ELSE 0 END) AS canWrite
, MAX(CASE WHEN p.permission = 'canDelete' THEN 1 ELSE 0 END) AS canDelete
FROM users u
LEFT JOIN permissions p
  ON p.userId = u.userId
GROUP BY u.userId, u.username, u.email, u.name;

CodePudding user response：

• Remove this (it seems to serve no purpose, but takes time):

    LEFT JOIN  permissions  ON permissions.userId = users.userId
  

• This index on permissions: INDEX(userId, permission) may help. (See caveat below.)

• Without the LEFT JOIN, the GROUP BY becomes unnecessary; get rid of it.

• Switch to EXISTS. For example

    (
        SELECT  DISTINCT permissions.permission
            FROM  permissions
            WHERE  permissions.permission = 'canRead'
              AND  users.userId = permissions.userId 
    ) IS NULL THEN 0 ELSE 1 END AS 'canRead',
  

-->

    EXISTS ( SELECT 1 FROM permissions
            WHERE  permissions.permission = 'canRead'
              AND  users.userId = permissions.userId 
    ) AS 'canRead',

Exists() is likely to be faster than the other Answers because EXISTS is a "semi-join", meaning that it stops when it discovers a matching row. The others have to check all the relevant rows, which takes longer.

• "users: (userId, username, email, name) permissions: (id, userId, permission)" implies that you have two unique keys on users? Is id needed at all? If you get rid of id and make userId the PRIMARY KEY, then my index recommendation above becomes redundant.

• For further discussion, please provide SHOW CREATE TABLE for each table.