Optimize participants list

What does this MR do?

Noticed that when I mention someone, the user list loading is quite slow.

Query

Old query:

SELECT Count(*)          AS count_all,
       members.source_id AS members_source_id
FROM   members
       INNER JOIN users
               ON users.id = members.user_id
WHERE  members.type = 'GroupMember'
       AND members.source_type = 'Namespace'
       AND members.source_id IN (SELECT namespaces.id
                                 FROM   ((SELECT namespaces.*
                                          FROM   namespaces
                                                 INNER JOIN members
                                                         ON namespaces.id =
                                                            members.source_id
                                          WHERE  members.type = 'GroupMember'
                                                 AND members.source_type =
                                                     'Namespace'
                                                 AND namespaces.type = 'Group'
                                                 AND members.user_id = 4156052
                                                 AND members.requested_at IS
                                                     NULL)
                                         UNION
                                         (SELECT namespaces.*
                                          FROM   projects
                                INNER JOIN project_authorizations
                                        ON projects.id =
           project_authorizations.project_id
           INNER JOIN namespaces
           ON namespaces.id = projects.namespace_id
           WHERE  project_authorizations.user_id = 4156052)) namespaces
           WHERE  namespaces.type = 'Group')
       AND members.requested_at IS NULL
GROUP  BY members.source_id  

We join users for no reason. Not sure why, maybe we didn't always had the CASCADE FK in place on the members table.

Plan - Note: sometimes I get much worse timings, usually between 80ms-400ms.

New query:

SELECT Count(*)          AS count_all,
       members.source_id AS members_source_id
FROM   members
WHERE  members.type = 'GroupMember'
       AND members.source_type = 'Namespace'
       AND members.source_id IN (SELECT namespaces.id
                                 FROM   ((SELECT namespaces.*
                                          FROM   namespaces
                                                 INNER JOIN members
                                                         ON namespaces.id =
                                                            members.source_id
                                          WHERE  members.type = 'GroupMember'
                                                 AND members.source_type =
                                                     'Namespace'
                                                 AND namespaces.type = 'Group'
                                                 AND members.user_id = 4156052
                                                 AND members.requested_at IS
                                                     NULL)
                                         UNION
                                         (SELECT namespaces.*
                                          FROM   projects
                                INNER JOIN project_authorizations
                                        ON projects.id =
           project_authorizations.project_id
           INNER JOIN namespaces
           ON namespaces.id = projects.namespace_id
           WHERE  project_authorizations.user_id = 4156052)) namespaces
           WHERE  namespaces.type = 'Group')
       AND members.requested_at IS NULL
GROUP  BY members.source_id  

Plan

Screenshots

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers
• Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team