Exceeded slow_query limit (1133.3 > 60.0) in mysql: ```

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (1133.3 > 60.0) in mysql: ``` #62930

Open vorpal-buildbot opened 9 months ago

vorpal-buildbot commented 9 months ago

    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

    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
        (d.id  IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Flowering of the White Tree')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) = 1)
     AND d.id  NOT IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Urza''s Ruinous Blast', 'Mountain')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) >= 1)
    ) 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
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 1133.3, mysql)

Reported on decksite by mysql-perf

Location Hash: 898f71bd4e95a013ab4ec21a8557d9b525693ee9

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2jAw.4VK8R7wv829qrjoOz7GxmUC1nlA'), ('deck_id', '234172'), ('views', '10058')]) Endpoint: post_archetypes View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'q': 'Flowering of the White Tree', 'notq': "Urza's Ruinous Blast\r\nMountain"} Content-Type: application/x-www-form-urlencoded Content-Length: 71 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 136.25.106.188 Cf-Ray: 84f73310fa971701-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "macOS" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Cookie: hide_intro=True; page_size=20; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI; deck_id=234172; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2jAw.4VK8R7wv829qrjoOz7GxmUC1nlA; views=10058 Priority: u=0, i Cf-Connecting-Ip: 136.25.106.188 Cdn-Loop: cloudflare Cf-Ipcountry: US ```

vorpal-buildbot commented 9 months ago

Exceeded slow_query limit (1093.5 > 60.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

    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
        (d.id  IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Flowering of the White Tree')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) = 1)
     AND d.id  NOT IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Mountain', 'Urza''s Ruinous Blast')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) >= 1)
    ) 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
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 1093.5, mysql)

Reported on decksite by mysql-perf

Location Hash: 898f71bd4e95a013ab4ec21a8557d9b525693ee9

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2mSQ.bt7A9-u3dSKCzw8CklPR4-X6-H0'), ('deck_id', '234172'), ('views', '10058')]) Endpoint: post_archetypes View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'q': 'Flowering of the White Tree', 'notq': "Mountain\r\nUrza's Ruinous Blast"} Content-Type: application/x-www-form-urlencoded Content-Length: 71 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 136.25.106.188 Cf-Ray: 84f7477c7e7c171a-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "macOS" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Cookie: hide_intro=True; page_size=20; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI; deck_id=234172; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2mSQ.bt7A9-u3dSKCzw8CklPR4-X6-H0; views=10058 Priority: u=0, i Cf-Connecting-Ip: 136.25.106.188 Cdn-Loop: cloudflare Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 9 months ago

Exceeded slow_query limit (244297.0 > 60.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

    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
        (d.id  IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Flowering of the White Tree', 'Geist of Saint Traft')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) = 2)
     AND d.id  NOT IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Mana Leak', 'Urza''s Ruinous Blast')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) >= 1)
    ) 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
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 244297.0, mysql)

Reported on decksite by mysql-perf

Location Hash: 898f71bd4e95a013ab4ec21a8557d9b525693ee9

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2fUw.MQBh5lLuf30VbfWkPE_UPBbnIkM'), ('deck_id', '234172'), ('views', '10058')]) Endpoint: post_archetypes View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'q': 'Flowering of the White Tree\r\nGeist of Saint Traft', 'notq': "Mana Leak\r\nUrza's Ruinous Blast"} Content-Type: application/x-www-form-urlencoded Content-Length: 98 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 136.25.106.188 Cf-Ray: 84f71ba6dc25fa82-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "macOS" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Cookie: hide_intro=True; page_size=20; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI; deck_id=234172; views=10058; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2fUw.MQBh5lLuf30VbfWkPE_UPBbnIkM Priority: u=0, i Cf-Connecting-Ip: 136.25.106.188 Cdn-Loop: cloudflare Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 9 months ago

Exceeded slow_query limit (244237.9 > 60.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

    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
        (d.id  IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Flowering of the White Tree', 'Island')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) = 2)
     AND d.id  NOT IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Urza''s Ruinous Blast', 'Mana Leak')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) >= 1)
    ) 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
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 244237.9, mysql)

Reported on decksite by mysql-perf

Location Hash: 898f71bd4e95a013ab4ec21a8557d9b525693ee9

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2g1g.XyzwjcMlz3HfdwlIvPmziKwVOok'), ('deck_id', '234172'), ('views', '10058')]) Endpoint: post_archetypes View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'q': 'Flowering of the White Tree\r\nIsland', 'notq': "Urza's Ruinous Blast\r\nMana Leak"} Content-Type: application/x-www-form-urlencoded Content-Length: 84 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 136.25.106.188 Cf-Ray: 84f7254efb6467f6-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "macOS" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Cookie: hide_intro=True; page_size=20; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI; deck_id=234172; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2g1g.XyzwjcMlz3HfdwlIvPmziKwVOok; views=10058 Priority: u=0, i Cf-Connecting-Ip: 136.25.106.188 Cdn-Loop: cloudflare Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 9 months ago

Exceeded slow_query limit (244071.0 > 60.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

    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
        (d.id  IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Flowering of the White Tree', 'Geist of Saint Traft')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) = 2)
     AND d.id  NOT IN (
        SELECT deck_id
        FROM deck_card
        WHERE card IN ('Urza''s Ruinous Blast', 'Mana Leak')
        GROUP BY deck_id
        HAVING COUNT(DISTINCT card) >= 1)
    ) 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
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 244071.0, mysql)

Reported on decksite by mysql-perf

Location Hash: 898f71bd4e95a013ab4ec21a8557d9b525693ee9

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2iKQ.Ykiid7oFh0d4oa7dtLl7AeOxmRw'), ('deck_id', '234172'), ('views', '10058')]) Endpoint: post_archetypes View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'q': 'Flowering of the White Tree\r\nGeist of Saint Traft\r\n', 'notq': "Urza's Ruinous Blast\r\nMana Leak"} Content-Type: application/x-www-form-urlencoded Content-Length: 104 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 136.25.106.188 Cf-Ray: 84f72e924e5067b9-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "macOS" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Cookie: hide_intro=True; page_size=20; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zac03w.q3w_xcoBRs6vIP2Wuggt1uBHJcI; deck_id=234172; views=10058; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.Zb2iKQ.Ykiid7oFh0d4oa7dtLl7AeOxmRw Cf-Connecting-Ip: 136.25.106.188 Cdn-Loop: cloudflare Cf-Ipcountry: US ```

Labels: decksite