Group root service slowness

[smadbe]

Sometimes, the group root service is very slow for Mathias' account while the output while it only outputs only a few 10s entries.

9sec in that case: (and that happens quite regularly, after a call the next ones are fast)

{
    "duration": "9.455621685s",
    "fileline": "/home/circleci/project/app/api/groups/get_roots.go:82",
    "level": "info",
    "msg": "WITH `matching_groups` AS ((SELECT ancestor_group.id, ancestor_group.type, ancestor_group.name FROM `groups_groups_active` JOIN groups_ancestors_active ON groups_ancestors_active.child_group_id = groups_groups_active.parent_group_id JOIN `groups` AS ancestor_group ON ancestor_group.id = groups_ancestors_active.ancestor_group_id WHERE (groups_groups_active.child_group_id = 1909944900306129841) AND (ancestor_group.type != 'ContestParticipants')) UNION (SELECT ancestor_group.id, ancestor_group.type, ancestor_group.name FROM `groups_ancestors_active` JOIN group_managers ON group_managers.group_id = `groups_ancestors_active`.ancestor_group_id JOIN groups_ancestors_active AS group_ancestors ON group_ancestors.ancestor_group_id = group_managers.manager_id AND group_ancestors.child_group_id = 1909944900306129841 JOIN `groups` ON groups.id = groups_ancestors_active.child_group_id AND groups.type != 'User' JOIN groups_ancestors_active AS ancestors_of_managed ON ancestors_of_managed.child_group_id = groups_ancestors_active.child_group_id JOIN `groups` AS ancestor_group ON ancestor_group.id = ancestors_of_managed.ancestor_group_id WHERE (ancestor_group.type != 'ContestParticipants')) ), `user_ancestors` AS (SELECT ancestor_group_id FROM `groups_ancestors_active` WHERE (child_group_id = 1909944900306129841)) SELECT groups.id, groups.type, groups.name, IF( EXISTS( SELECT 1 FROM groups_groups_active WHERE groups_groups_active.parent_group_id = groups.id AND groups_groups_active.child_group_id = 1909944900306129841 ), 'direct', IF( EXISTS( SELECT 1 FROM groups_groups_active JOIN groups_ancestors_active AS group_descendants ON group_descendants.ancestor_group_id = groups.id AND group_descendants.child_group_id = groups_groups_active.parent_group_id WHERE groups_groups_active.child_group_id = 1909944900306129841 ), 'descendant', 'none' ) ) AS 'current_user_membership', IF( EXISTS( SELECT 1 FROM user_ancestors JOIN group_managers ON group_managers.group_id = groups.id AND group_managers.manager_id = user_ancestors.ancestor_group_id ), 'direct', IF( EXISTS( SELECT 1 FROM user_ancestors JOIN groups_ancestors_active AS group_ancestors ON group_ancestors.child_group_id = groups.id JOIN group_managers ON group_managers.group_id = group_ancestors.ancestor_group_id AND group_managers.manager_id = user_ancestors.ancestor_group_id ), 'ancestor', IF( EXISTS( SELECT 1 FROM user_ancestors JOIN group_managers ON group_managers.manager_id = user_ancestors.ancestor_group_id JOIN groups_ancestors_active AS managed_groups ON managed_groups.ancestor_group_id = group_managers.group_id JOIN `groups` AS managed_descendant ON managed_descendant.id = managed_groups.child_group_id AND managed_descendant.type != 'User' JOIN groups_ancestors_active AS group_descendants ON group_descendants.ancestor_group_id = groups.id AND group_descendants.child_group_id = managed_descendant.id ), 'descendant', 'none' ) ) ) AS 'current_user_managership' FROM matching_groups AS `groups` WHERE (groups.type != 'Base') AND ( NOT EXISTS( SELECT 1 FROM `groups` AS parent_group JOIN groups_groups_active ON groups_groups_active.parent_group_id = parent_group.id AND groups_groups_active.child_group_id = groups.id WHERE parent_group.type != 'Base' )) ORDER BY `groups`.`name`",
    "time": "2025-02-12T10:48:42.838Z",
    "type": "db"
}

Check if you don't see a way to improve it or just have an explanation which would allow us to find another way to use that service or to change that request.