Fix Most Quit/Crashed Calculation

[iBotPeaches]

Fix Most Quit/Crashed Calculation

[iBotPeaches]

cc @chase-patterson - maybe you can help me with this. Unsure if best to do a sub query to find all IDs for each name and work calculation that way.

[chase-patterson]

You want to consider each set of Maps with the same name as a "map" for this calculation?

In the subquery, join with maps and instead of grouping by map_id, group by maps.name. I think that'll work.

Then you don't need to join this with a Map builder anymore because they indeed don't represent actual maps anymore.

[iBotPeaches]

Takes ~20min to generate, so need to optimize.

MariaDB [leafapp]> select maps.name as map_name, outcome, count(*) as total from `games` right join `game_players` on `games`.`id` = `game_players`.`game_id` right join `maps` on `maps`.`id` = `games`.`map_id` where `games`.`playlist_id` is not null and `games`.`gamevariant_id` not in (1) group by `map_name`, `outcome`;
+---------------+---------+---------+
| map_name      | outcome | total   |
+---------------+---------+---------+
| Absolution    |       1 |    2753 |
| Absolution    |       2 |    2119 |
| Absolution    |       3 |     328 |
| Absolution    |       4 |       8 |
| Aquarius      |       1 | 1168315 |
| Aquarius      |       2 | 1004997 |
| Aquarius      |       3 |  100062 |
| Aquarius      |       4 |    5679 |
| Argyle        |       1 |   41339 |
| Argyle        |       2 |   33013 |
| Argyle        |       3 |    5381 |
| Argyle        |       4 |     969 |
| Bazaar        |       1 | 1014925 |
| Bazaar        |       2 |  867728 |
| Bazaar        |       3 |  115768 |
| Bazaar        |       4 |   20289 |
| Behemoth      |       1 |  563738 |
| Behemoth      |       2 |  467380 |
| Behemoth      |       3 |   78636 |
| Behemoth      |       4 |    9974 |
| Breaker       |       1 |  251155 |
| Breaker       |       2 |  279574 |
| Breaker       |       3 |  237204 |
| Breaker       |       4 |   25341 |
| Catalyst      |       1 |  462047 |
| Catalyst      |       2 |  431603 |
| Catalyst      |       3 |   54908 |
| Catalyst      |       4 |    2477 |
| Chasm         |       1 |   13837 |
| Chasm         |       2 |   12446 |
| Chasm         |       3 |    2575 |
| Chasm         |       4 |     232 |
| Cliffhanger   |       1 |   13836 |
| Cliffhanger   |       2 |   12657 |
| Cliffhanger   |       3 |    2586 |
| Cliffhanger   |       4 |     224 |
| Curfew        |       1 |     755 |
| Curfew        |       2 |     569 |
| Curfew        |       3 |      85 |
| Curfew        |       4 |       4 |
| Deadlock      |       1 |  389425 |
| Deadlock      |       2 |  302637 |
| Deadlock      |       3 |  116339 |
| Deadlock      |       4 |   31250 |
| Detachment    |       1 |   31249 |
| Detachment    |       2 |   26131 |
| Detachment    |       3 |    5063 |
| Detachment    |       4 |     536 |
| Ecliptic      |       1 |     132 |
| Ecliptic      |       2 |      77 |
| Ecliptic      |       3 |      11 |
| Empyrean      |       1 |  105151 |
| Empyrean      |       2 |   86989 |
| Empyrean      |       3 |   12467 |
| Empyrean      |       4 |    2046 |
| Fragmentation |       1 |  387176 |
| Fragmentation |       2 |  305488 |
| Fragmentation |       3 |  114612 |
| Fragmentation |       4 |   37835 |
| Highpower     |       1 |  397649 |
| Highpower     |       2 |  301560 |
| Highpower     |       3 |  116909 |
| Highpower     |       4 |   24374 |
| Institute     |       1 |   11615 |
| Institute     |       2 |    9275 |
| Institute     |       3 |    1711 |
| Institute     |       4 |     206 |
| Launch Site   |       1 |  357640 |
| Launch Site   |       2 |  301993 |
| Launch Site   |       3 |   77236 |
| Launch Site   |       4 |   33775 |
| Live Fire     |       1 | 1790788 |
| Live Fire     |       2 | 1563958 |
| Live Fire     |       3 |  144414 |
| Live Fire     |       4 |    2784 |
| Mires         |       1 |     100 |
| Mires         |       2 |      82 |
| Mires         |       3 |      16 |
| Oasis         |       1 |   19232 |
| Oasis         |       2 |   11822 |
| Oasis         |       3 |    2648 |
| Oasis         |       4 |     991 |
| Perilous      |       1 |    2672 |
| Perilous      |       2 |    2110 |
| Perilous      |       3 |     377 |
| Perilous      |       4 |       2 |
| Recharge      |       1 | 1732636 |
| Recharge      |       2 | 1483445 |
| Recharge      |       3 |  136631 |
| Recharge      |       4 |    2973 |
| Salvation     |       1 |    3910 |
| Salvation     |       2 |    3001 |
| Salvation     |       3 |     943 |
| Salvation     |       4 |      56 |
| Solution      |       1 |     931 |
| Solution      |       2 |     759 |
| Solution      |       3 |     150 |
| Starboard     |       1 |    3385 |
| Starboard     |       2 |    2639 |
| Starboard     |       3 |     506 |
| Starboard     |       4 |      23 |
| Streets       |       1 | 1771412 |
| Streets       |       2 | 1515161 |
| Streets       |       3 |  158070 |
| Streets       |       4 |   10546 |
| Vagabond      |       1 |     794 |
| Vagabond      |       2 |     663 |
| Vagabond      |       3 |     115 |
| Vagabond      |       4 |       1 |
+---------------+---------+---------+
109 rows in set (18 min 6.325 sec)

[chase-patterson]

Was it significantly faster before that early join? To get the same performance as before, you could denormalize and put map name on the games table. Even better, denormalize the game_players table, and if you add a couple other columns too, you can do entire player game pages and csv exports without all those joins.

You could also try switching those to inner joins--it might be able to optimize better. Idk why I had a right join in the original query--it shouldn't be.

[iBotPeaches]

SELECT
    maps.name AS map_name,
    outcome,
    COUNT(*) AS total
FROM
    `games`
RIGHT JOIN `game_players` ON `games`.`id` = `game_players`.`game_id`
RIGHT JOIN `maps` ON `maps`.`id` = `games`.`map_id`
WHERE
    `games`.`playlist_id` = 43
GROUP BY
    `map_name`,
    `outcome`;

Saving this. Someone wanted quit percents for Husky Raid playlist.