vorpal-buildbot
commented
6 months ago Exceeded slow_query limit (1.3 > 1.0) in mysql: ```
SELECT
p.id,
LOWER(IFNULL(IFNULL(IFNULL(p.name, p.mtgo_username), p.mtggoldfish_username), p.tappedout_username)) AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
p.locale,
p.banned,
SUM(1) AS num_decks,
SUM(dc.wins) AS wins,
SUM(dc.losses) AS losses,
SUM(dc.draws) AS draws,
SUM(dc.wins - dc.losses) AS record,
SUM(CASE WHEN dc.wins >= 5 AND dc.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(dc.wins) / NULLIF(SUM(dc.wins + dc.losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions,
COUNT(*) OVER () AS total
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
WHERE
(TRUE) AND (TRUE)
GROUP BY
p.id
ORDER BY
elo DESC, num_decks DESC, record, name
LIMIT 0, 20
```[] (slow_query, 1.3, mysql)
Reported on decksite by mysql-perf
Location Hash: 0c49440b9235d3ff4f0b9f6870440b8e1706c050
Request Data
``` Request Method: GET Path: /api/people/?deckType=all&page=0&pageSize=20&q=&sortBy=elo&sortOrder=DESC&seasonId=0 Cookies: ImmutableMultiDict([('page_size', '20'), ('hide_intro', 'True'), ('views', '31')]) Endpoint: people_api View Args: {} Person: logged_out Referrer: https://pennydreadfulmagic.com/seasons/all/people/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 2403:4800:2543:a418:514a:41ba:1335:fb9a Cf-Ray: 8863d4afe881a932-SYD X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Sec-Ch-Ua: "Chromium";v="124", "Google Chrome";v="124", "Not-A.Brand";v="99" Accept: application/json, text/plain, */* Sec-Ch-Ua-Mobile: ?0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36 Sec-Ch-Ua-Platform: "Windows" Sec-Fetch-Site: same-origin Sec-Fetch-Mode: cors Sec-Fetch-Dest: empty Referer: https://pennydreadfulmagic.com/seasons/all/people/ Accept-Language: en-GB,en;q=0.9 Priority: u=1, i Cookie: page_size=20; hide_intro=True; views=31 Cf-Connecting-Ip: 2403:4800:2543:a418:514a:41ba:1335:fb9a Cdn-Loop: cloudflare Cf-Ipcountry: AU ```Labels: decksite
[](slow_query, 1.2, mysql)Reported on decksite by mysql-perf
Location Hash: 0c49440b9235d3ff4f0b9f6870440b8e1706c050
Request Data
``` Request Method: GET Path: /api/people/?deckType=all&page=0&pageSize=20&q=&sortBy=elo&sortOrder=DESC&seasonId=0 Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UVFPwjAY_C99nmQdW2G8oQkIEojgkGjMUtZvW9nWLm2HIuG_22EQY-Jb77u73tfrEcU1qIoKEAYNjGrAQZRVXFwAg4pXkqFBSkvdYq4TqVjM7QjhMCC-H-Ke28e-73okQFdFKRNaglWBuIlWlvjfwkWcNbxkl9BfVtSiLAPW7qmlOAdjv-ugymQybjQoQatWu6UFKBOG1iFpY3Iv1oaallk85pvJcD-ezordPLpbDnN4z-8avV1n3MyueiMLGzg4IpokoPUFo8ln6ia58l4iFQXP3no16m32-X1Q4nSmnqwfPmquQMfUdohJLyRhlxCvg-3Jw1e6bZW4ft91HaQgtaP8J8MldeKzqWBjVcjRVtU5Jmyxe1i60Xx5sBm21No-5hWdm9LnPu2n8bQlv2ednbQRbw46XxqbQ2tAt0AVKHRy0N8GDVUZ2JVFU5anLysCqKs.Zj-82A.WEkxaw8Dsp7zRg1sKZfXXGpwe1Y'), ('session', '.eJx1UVFPwjAY_C99nmQdW2G8oQkIEojgkGjMUtZvW9nWLm2HIuG_22EQY-Jb77u73tfrEcU1qIoKEAYNjGrAQZRVXFwAg4pXkqFBSkvdYq4TqVjM7QjhMCC-H-Ke28e-73okQFdFKRNaglWBuIlWlvjfwkWcNbxkl9BfVtSiLAPW7qmlOAdjv-ugymQybjQoQatWu6UFKBOG1iFpY3Iv1oaallk85pvJcD-ezordPLpbDnN4z-8avV1n3MyueiMLGzg4IpokoPUFo8ln6ia58l4iFQXP3no16m32-X1Q4nSmnqwfPmquQMfUdohJLyRhlxCvg-3Jw1e6bZW4ft91HaQgtaP8J8MldeKzqWBjVcjRVtU5Jmyxe1i60Xx5sBm21No-5hWdm9LnPu2n8bQlv2ednbQRbw46XxqbQ2tAt0AVKHRy0N8GDVUZ2JVFU5anLysCqKs.ZkkZJA.7Z7GbwCJ9UiaGEdpSyRfFUtDUzk'), ('deck_id', '242699'), ('page_size', '20'), ('views', '10156')]) Endpoint: people_api View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/seasons/all/people/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 136.25.106.188 Cf-Ray: 885ed4d5ab9996cc-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Sec-Ch-Ua: "Chromium";v="124", "Google Chrome";v="124", "Not-A.Brand";v="99" Accept: application/json, text/plain, */* Sec-Ch-Ua-Mobile: ?0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36 Sec-Ch-Ua-Platform: "macOS" Sec-Fetch-Site: same-origin Sec-Fetch-Mode: cors Sec-Fetch-Dest: empty Referer: https://pennydreadfulmagic.com/seasons/all/people/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Priority: u=1, i Cookie: hide_intro=True; session=.eJx1UVFPwjAY_C99nmQdW2G8oQkIEojgkGjMUtZvW9nWLm2HIuG_22EQY-Jb77u73tfrEcU1qIoKEAYNjGrAQZRVXFwAg4pXkqFBSkvdYq4TqVjM7QjhMCC-H-Ke28e-73okQFdFKRNaglWBuIlWlvjfwkWcNbxkl9BfVtSiLAPW7qmlOAdjv-ugymQybjQoQatWu6UFKBOG1iFpY3Iv1oaallk85pvJcD-ezordPLpbDnN4z-8avV1n3MyueiMLGzg4IpokoPUFo8ln6ia58l4iFQXP3no16m32-X1Q4nSmnqwfPmquQMfUdohJLyRhlxCvg-3Jw1e6bZW4ft91HaQgtaP8J8MldeKzqWBjVcjRVtU5Jmyxe1i60Xx5sBm21No-5hWdm9LnPu2n8bQlv2ednbQRbw46XxqbQ2tAt0AVKHRy0N8GDVUZ2JVFU5anLysCqKs.Zj-82A.WEkxaw8Dsp7zRg1sKZfXXGpwe1Y; deck_id=242699; page_size=20; views=10156; session=.eJx1UVFPwjAY_C99nmQdW2G8oQkIEojgkGjMUtZvW9nWLm2HIuG_22EQY-Jb77u73tfrEcU1qIoKEAYNjGrAQZRVXFwAg4pXkqFBSkvdYq4TqVjM7QjhMCC-H-Ke28e-73okQFdFKRNaglWBuIlWlvjfwkWcNbxkl9BfVtSiLAPW7qmlOAdjv-ugymQybjQoQatWu6UFKBOG1iFpY3Iv1oaallk85pvJcD-ezordPLpbDnN4z-8avV1n3MyueiMLGzg4IpokoPUFo8ln6ia58l4iFQXP3no16m32-X1Q4nSmnqwfPmquQMfUdohJLyRhlxCvg-3Jw1e6bZW4ft91HaQgtaP8J8MldeKzqWBjVcjRVtU5Jmyxe1i60Xx5sBm21No-5hWdm9LnPu2n8bQlv2ednbQRbw46XxqbQ2tAt0AVKHRy0N8GDVUZ2JVFU5anLysCqKs.ZkkZJA.7Z7GbwCJ9UiaGEdpSyRfFUtDUzk Cf-Connecting-Ip: 136.25.106.188 Cdn-Loop: cloudflare Cf-Ipcountry: US ```