Exceeded slow_query limit (3.7 > 1.0) in mysql: ```

SELECT d.id, d.finish, d.decklist_hash, cache.active_date, cache.wins, cache.losses, cache.draws, cache.color_sort, ct.name AS competition_type_name, COUNT(*) OVER () AS total FROM deck AS d LEFT JOIN competition AS c ON d.competition_id = c.id LEFT JOIN competition_series AS cs ON cs.id = c.competition_series_id LEFT JOIN competition_type AS ct ON ct.id = cs.competition_type_id LEFT JOIN deck_cache AS cache ON d.id = cache.deck_id LEFT JOIN deck_cache AS season ON d.id = season.deck_id WHERE (TRUE) AND (d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = 3)) AND (TRUE) GROUP BY d.id, d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this. season.season_id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs. HAVING TRUE ORDER BY cache.active_date DESC, d.finish ASC, cache.active_date DESC LIMIT 0, 100 ```

Exceeded slow_query limit (1.1 > 1.0) in mysql: ```

    SELECT

    d.id,
    d.finish,
    d.decklist_hash,
    cache.active_date,
    cache.wins,
    cache.losses,
    cache.draws,
    cache.color_sort,
    ct.name AS competition_type_name,
    COUNT(*) OVER () AS total

    FROM
        deck AS d

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN
        deck_cache AS cache ON d.id = cache.deck_id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    WHERE
        (TRUE) AND (
    d.retired
    OR
    IFNULL(ct.name, '') <> 'League'
    OR
    IFNULL(cache.wins, 0) + IFNULL(cache.draws, 0) + IFNULL(cache.losses, 0) >= 5
    OR
    c.end_date < UNIX_TIMESTAMP(NOW())
OR d.person_id = 8786) AND (d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = 3)) AND (TRUE)
    GROUP BY 
        d.id,
        d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
        season.season_id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.

    HAVING
        TRUE
    ORDER BY
        cache.active_date DESC, d.finish ASC, cache.active_date DESC
    LIMIT 0, 100
```

[] (slow_query, 1.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 179ff7efaba83d4e8eeb45920b4be4a13395dbbe

Request Data

``` Request Method: GET Path: /api/decks/?achievementKey=&archetypeId=3&cardName=&competitionId=&competitionFlagId=&deckType=all&page=0&pageSize=100&personId=&q=&seasonId=0 Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('hide_intro', 'True'), ('session', '.eJx1UctOwzAQ_BefA3LSNna4UVSJqqU8KkCAUOQm28RJbAfboUDFv7MpantA3Lw7uzM74y1JW7BKaNCenHnbQUBErqQmZ2vROKxyUFKZfA_m0mXG5qnEDmGMshGP6CgJk2EcDnhMjhONyUQDOAX65H6JwP8rUqdFJ5uDSGOKAvL-Mmf0ToozHgdE-cKknQOrheqZfQkb4bNyI3QOFomM6HwZpc4L3-MfF3Xl8_fnx4G5XnEznwhTf9nrerVYzJ6647w3NaDjLRFZBs7ta1IP2Gw6DWt6uZTtcr66etZeDcrNw5gl4wnuw0crLbhUYHghOuNsSCk_jaKIh2F0xPs8YzrklAbEwhpb5UFk1cS0uqtCFb3BJpuyc7O-Ubdwp1mdzCoUwThbdPNCdhk57Pw-TiuDvH2u-Hly_UleA7IjTf1nv0DGICzG8h2QP0l6YQvAo3XXNN8_xU6oLw.Ziv_Cg.AC_lL2I-F9Dj54IdcerOTUCKsCc'), ('session', '.eJx1UctOwzAQ_BefA3LSNna4UVSJqqU8KkCAUOQm28RJbAfboUDFv7MpantA3Lw7uzM74y1JW7BKaNCenHnbQUBErqQmZ2vROKxyUFKZfA_m0mXG5qnEDmGMshGP6CgJk2EcDnhMjhONyUQDOAX65H6JwP8rUqdFJ5uDSGOKAvL-Mmf0ToozHgdE-cKknQOrheqZfQkb4bNyI3QOFomM6HwZpc4L3-MfF3Xl8_fnx4G5XnEznwhTf9nrerVYzJ6647w3NaDjLRFZBs7ta1IP2Gw6DWt6uZTtcr66etZeDcrNw5gl4wnuw0crLbhUYHghOuNsSCk_jaKIh2F0xPs8YzrklAbEwhpb5UFk1cS0uqtCFb3BJpuyc7O-Ubdwp1mdzCoUwThbdPNCdhk57Pw-TiuDvH2u-Hly_UleA7IjTf1nv0DGICzG8h2QP0l6YQvAo3XXNN8_xU6oLw.Zkl15A.x9Iv2nIdGdUoXAsG7LHUqjbO-L0'), ('page_size', '100'), ('views', '306')]) Endpoint: decks_api View Args: {} Person: 770758205919461386 Referrer: https://pennydreadfulmagic.com/seasons/all/archetypes/control/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 47.26.243.253 Cf-Ray: 88611871c8b32312-ORD 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, */* Dnt: 1 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/archetypes/control/ Accept-Language: en-US,en;q=0.9 Priority: u=1, i Cookie: hide_intro=True; hide_intro=True; session=.eJx1UctOwzAQ_BefA3LSNna4UVSJqqU8KkCAUOQm28RJbAfboUDFv7MpantA3Lw7uzM74y1JW7BKaNCenHnbQUBErqQmZ2vROKxyUFKZfA_m0mXG5qnEDmGMshGP6CgJk2EcDnhMjhONyUQDOAX65H6JwP8rUqdFJ5uDSGOKAvL-Mmf0ToozHgdE-cKknQOrheqZfQkb4bNyI3QOFomM6HwZpc4L3-MfF3Xl8_fnx4G5XnEznwhTf9nrerVYzJ6647w3NaDjLRFZBs7ta1IP2Gw6DWt6uZTtcr66etZeDcrNw5gl4wnuw0crLbhUYHghOuNsSCk_jaKIh2F0xPs8YzrklAbEwhpb5UFk1cS0uqtCFb3BJpuyc7O-Ubdwp1mdzCoUwThbdPNCdhk57Pw-TiuDvH2u-Hly_UleA7IjTf1nv0DGICzG8h2QP0l6YQvAo3XXNN8_xU6oLw.Ziv_Cg.AC_lL2I-F9Dj54IdcerOTUCKsCc; page_size=100; views=306; session=.eJx1UctOwzAQ_BefA3LSNna4UVSJqqU8KkCAUOQm28RJbAfboUDFv7MpantA3Lw7uzM74y1JW7BKaNCenHnbQUBErqQmZ2vROKxyUFKZfA_m0mXG5qnEDmGMshGP6CgJk2EcDnhMjhONyUQDOAX65H6JwP8rUqdFJ5uDSGOKAvL-Mmf0ToozHgdE-cKknQOrheqZfQkb4bNyI3QOFomM6HwZpc4L3-MfF3Xl8_fnx4G5XnEznwhTf9nrerVYzJ6647w3NaDjLRFZBs7ta1IP2Gw6DWt6uZTtcr66etZeDcrNw5gl4wnuw0crLbhUYHghOuNsSCk_jaKIh2F0xPs8YzrklAbEwhpb5UFk1cS0uqtCFb3BJpuyc7O-Ubdwp1mdzCoUwThbdPNCdhk57Pw-TiuDvH2u-Hly_UleA7IjTf1nv0DGICzG8h2QP0l6YQvAo3XXNN8_xU6oLw.Zkl15A.x9Iv2nIdGdUoXAsG7LHUqjbO-L0 Cf-Connecting-Ip: 47.26.243.253 Cdn-Loop: cloudflare Cf-Ipcountry: US ```

Labels: decksite

PennyDreadfulMTG / perf-reports

Exceeded slow_query limit (3.7 > 1.0) in mysql: ``` #63323