Bad SQL after #497

[magnified103]

Endpoints:

• /organization/{org}/submissions/
• /submissions/?organization={org}

create extremely slow SQL requests (queries that last 70 seconds).

Root cause

SELECT *
  FROM `judge_submission`
STRAIGHT_JOIN `judge_problem`
    ON (`judge_submission`.`problem_id` = `judge_problem`.`id`)
STRAIGHT_JOIN (
        SELECT `judge_problem`.`id`
          FROM `judge_problem`
       ) visible_problems
    ON (`judge_submission`.`problem_id` = visible_problems.`id`)
STRAIGHT_JOIN `judge_profile`
    ON (`judge_submission`.`user_id` = `judge_profile`.`id`)
STRAIGHT_JOIN `auth_user`
    ON (`judge_profile`.`user_id` = `auth_user`.`id`)
  LEFT OUTER JOIN `judge_badge`
    ON (`judge_profile`.`display_badge_id` = `judge_badge`.`id`)
STRAIGHT_JOIN `judge_language`
    ON (`judge_submission`.`language_id` = `judge_language`.`id`)
  LEFT OUTER JOIN `judge_contest`
    ON (`judge_submission`.`contest_object_id` = `judge_contest`.`id`)
 WHERE `judge_problem`.`organization_id` = 80
 ORDER BY `judge_submission`.`id` DESC
 LIMIT 50

During the analysis, it seems that the JOIN with judge_problem (and the org WHERE filter) was done after a full table scan of judge_submission with more than 10M rows.