Indexes lacking in securitygroups_acl_roles

[SinergiaCRM]

Issue

The table securitygroups_acl_roles wich relates security groups with roles only has the primary index but not the needed index to query the roles assigned to a security group.
In situations where a lot of SGs exist this can cause a problem due to the excessive time wated by the query.

Possible Fix

Include the index definition via vardefs

Steps to Reproduce the Issue

1.Define a lot of Security groups (we have more than 500) with one or more roles assigned to each one
2.Define some users and assign to them some of the created groups
3.Make concurrent logins (we had more than 20 users trying to login at the same time)
4.Execute a SHOW FULL PROCESSLIST and observe that a query similar to the following one is getting stuck:
`(SELECT acl_actions .*, acl_roles_actions.access_override, 1 as user_role
FROM acl_actions
INNER JOIN acl_roles_users ON acl_roles_users.user_id =
'dfce34bb-a134-3b87-66c8-665db32ee072' AND acl_roles_users.deleted = 0
LEFT JOIN acl_roles_actions ON acl_roles_actions.role_id =
acl_roles_users.role_id AND acl_roles_actions.action_id = acl_actions.id AND
acl_roles_actions.deleted=0
WHERE acl_actions.deleted=0 )
UNION
(SELECT acl_actions .*, acl_roles_actions.access_override, 0 as
user_role
 FROM acl_actions
INNER JOIN securitygroups_users ON securitygroups_users.user_id
 = 'dfce34bb-a134-3b87-66c8-665db32ee072' AND securitygroups_users.deleted = 0
 INNER JOIN securitygroups_acl_roles ON
securitygroups_users.securitygroup_id = securitygroups_acl_roles.securitygroup_id and
securitygroups_acl_roles.deleted = 0
LEFT JOIN acl_roles_actions ON acl_roles_actions.role_id =
securitygroups_acl_roles.role_id AND acl_roles_actions.action_id = acl_actions.id AND
acl_roles_actions.deleted=0
WHERE acl_actions.deleted=0 )
UNION
(SELECT acl_actions.*, 0 as access_override, -1 as user_role
FROM acl_actions
WHERE acl_actions.deleted = 0 )
ORDER BY user_role desc, category,name,access_override desc`

Context

Those query getting stuck made that some user could not login until some minutes passed.

Version

7.12.12

What browser are you currently using?

Chrome

Browser Version

No response

Environment Information

MAriaDB, PHP 7.4

Operating System and Version

Debian