Scoreboard needs optimization

[werebus]

Not sure of the best way to refactor this, but the scoreboard is super slow to load.

I believe one of the biggest culprets is determining whether to show the participant's name or not. To do that, we calculate everyone's score to see who the top 20 are.

[werebus]

I spent some time this afternoon crafting this artisanal query

SELECT (DENSE_RANK() OVER w) <= 20 AS top_twenty,
       p.*, mp.id, mp.score,
       m.maneuver_score,
       oj.score AS onboard_judging_score,
       m.maneuver_score + oj.score AS maneuver_total,
       (q.points_achieved / q.total_points) * 50 AS quiz_score,
       (cc.defects_found / cc.total_defects) * 50 AS circle_check_score,
       m.maneuver_score + oj.score
                        + ((q.points_achieved / q.total_points) * 50)
                        + ((cc.defects_found / cc.total_defects) * 50)
                        AS total_score
FROM participants p
LEFT JOIN (SELECT participant_id, SUM(score) maneuver_score FROM maneuver_participants GROUP BY participant_id) m
          ON p.id = m.participant_id
LEFT JOIN maneuver_participants mp ON p.id = mp.participant_id
LEFT JOIN onboard_judgings oj ON oj.participant_id = p.id
LEFT JOIN quiz_scores q ON q.participant_id = p.id
LEFT JOIN circle_check_scores cc ON cc.participant_id = p.id
WINDOW w AS (ORDER BY total_score DESC, p.id)

I think this can probably be pulled apart in a way that Active Record likes. I have some more thoughts, but I wanted to paste this somewhere before I accidentally closed my MySQL window.