Optimize query in getGroupActivities

A query that takes > 8s in production:
SELECT 
  items.*, 
  items.id AS item_id, 
  COALESCE(
    user_strings.title, default_strings.title
  ) AS title, 
  IF(
    user_strings.title IS NOT NULL, user_strings.language_tag, 
    default_strings.language_tag
  ) AS language_tag 
FROM 
  (
    SELECT 
      groups.id AS group_id, 
      groups.name AS group_name, 
      groups.type AS group_type, 
      groups.created_at, 
      items.id, 
      items.type AS item_type, 
      items.requires_explicit_entry, 
      items.entry_participant_type, 
      items.no_score, 
      items.default_language_tag, 
      IFNULL(
        (
          SELECT 
            MAX(results.score_computed) AS best_score 
          FROM 
            results 
          WHERE 
            results.item_id = items.id 
            AND results.participant_id = 1909944900306129841
        ), 
        0
      ) AS best_score, 
      can_grant_view_generated_value, 
      can_watch_generated_value, 
      can_edit_generated_value, 
      is_owner_generated, 
      can_view_generated_value, 
      attempts.allows_submissions_until AS attempt_allows_submissions_until, 
      IFNULL(
        (
          SELECT 
            1 
          FROM 
            permissions_generated AS permissions 
            JOIN groups_ancestors_active AS ancestors ON ancestors.child_group_id = 1909944900306129841 
            AND ancestors.ancestor_group_id = permissions.group_id 
            JOIN items_items ON items_items.child_item_id = permissions.item_id 
          WHERE 
            (
              IFNULL(can_view_generated_value, 1) >= 2
            ) 
            AND (
              items_items.parent_item_id = items.id
            ) 
          LIMIT 
            1
        ), 0
      ) AS has_visible_children, 
      results.attempt_id, 
      results.score_computed, 
      results.validated, 
      results.started_at, 
      results.latest_activity_at, 
      attempts.ended_at 
    FROM 
      `groups_ancestors_active` 
      JOIN `groups` ON groups.id = groups_ancestors_active.ancestor_group_id 
      JOIN items ON items.id = groups.root_activity_id 
      JOIN LATERAL (
        SELECT 
          permissions.item_id, 
          MAX(can_view_generated_value) AS can_view_generated_value, 
          MAX(can_grant_view_generated_value) AS can_grant_view_generated_value, 
          MAX(can_watch_generated_value) AS can_watch_generated_value, 
          MAX(can_edit_generated_value) AS can_edit_generated_value, 
          MAX(is_owner_generated) AS is_owner_generated 
        FROM 
          permissions_generated AS permissions 
          JOIN groups_ancestors_active AS ancestors ON ancestors.ancestor_group_id = permissions.group_id 
        WHERE 
          (
            ancestors.child_group_id = 1909944900306129841
          ) 
          AND (permissions.item_id = items.id) 
        GROUP BY 
          permissions.item_id 
        HAVING 
          (
            MAX(can_view_generated_value) >= 2
          )
      ) AS permissions ON permissions.item_id = items.id 
      LEFT JOIN results ON results.participant_id = 1909944900306129841 
      AND results.item_id = items.id 
      LEFT JOIN attempts ON attempts.participant_id = results.participant_id 
      AND attempts.id = results.attempt_id 
    WHERE 
      (
        groups_ancestors_active.child_group_id = 1909944900306129841 
        OR groups_ancestors_active.child_group_id IN(
          (
            SELECT 
              group_managers.group_id AS id 
            FROM 
              `group_managers` 
              JOIN groups_ancestors_active ON groups_ancestors_active.ancestor_group_id = group_managers.manager_id 
              AND groups_ancestors_active.child_group_id = 1909944900306129841
          )
        )
      ) 
    GROUP BY 
      groups.id, 
      results.participant_id, 
      results.attempt_id
  ) AS items 
  LEFT JOIN items_strings default_strings ON default_strings.item_id = items.id 
  AND default_strings.language_tag = items.default_language_tag 
  LEFT JOIN items_strings user_strings ON user_strings.item_id = items.id 
  AND user_strings.language_tag = "fr" 
ORDER BY 
  items.created_at, 
  items.group_id, 
  items.attempt_id
It looks like the optimizer cannot use the index on child_group_id with the OR because it breaks the ordering.
The query is faster if we include the current group id with the others, and sort the result.
France-ioi / AlgoreaBackend

Optimize query in getGroupActivities #1069

Codecov Report
