vorpal-buildbot
commented
4 years ago Exceeded slow_query limit (85.1 > 30.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
(
SELECT
`start`.id,
`start`.code,
`start`.start_date AS start_date,
`end`.start_date AS end_date
FROM
season AS `start`
LEFT JOIN
season AS `end` ON `end`.id = `start`.id + 1
) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)
WHERE
(ct.name = 'League' AND d.created_date > 0 AND d.created_date <= 1605015464) 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.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
wins >= 5 AND losses = 0
ORDER BY
active_date DESC, d.finish IS NULL, d.finish
LIMIT 10
```[] (slow_query, 85.1, mysql)
Reported on decksite by mysql-perf
Location Hash: d8bc71d414409f395e7e893227c05b3246aef841
Request Data
``` Request Method: HEAD Path: /? Cookies: ImmutableMultiDict([]) Endpoint: home View Args: {} Person: logged_out Referrer: https://pennydreadfulmagic.com Request Data: {} Host: pennydreadfulmagic.com Accept-Encoding: gzip Cf-Ipcountry: US X-Forwarded-For: 69.162.124.229, 172.68.141.132 Cf-Ray: 5f002a105837b3c4-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} User-Agent: Mozilla/5.0+(compatible; UptimeRobot/2.0; http://www.uptimerobot.com/) Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8 Accept-Language: en-US,en;q=0.8 Accept-Charset: ISO-8859-1,UTF-8;q=0.7,*;q=0.7 Cache-Control: no-cache Referer: https://pennydreadfulmagic.com Cf-Request-Id: 0653f89e380000b3c41410e000000001 Cf-Connecting-Ip: 69.162.124.229 Cdn-Loop: cloudflare X-Forwarded-Host: pennydreadfulmagic.com X-Forwarded-Server: pennydreadfulmagic.com Content-Length: 0 Connection: Keep-Alive ```Labels: decksite
[](slow_query, 1043.7, mysql)Reported on decksite by mysql-perf
Location Hash: d8bc71d414409f395e7e893227c05b3246aef841
Request Data
``` Request Method: GET Path: /? Cookies: ImmutableMultiDict([('__cfduid', 'd40d91cd3bcc6d0b26e6153ee87bca1041599749270'), ('_ga', 'GA1.2.1080008113.1599749271'), ('_gid', 'GA1.2.2098970943.1599749271'), ('hide_intro', 'True'), ('page_size', '100'), ('views', '47')]) Endpoint: home View Args: {} Person: logged_out Referrer: https://www.google.com/ Request Data: {} Host: pennydreadfulmagic.com Accept-Encoding: gzip Cf-Ipcountry: US X-Forwarded-For: 4.28.60.194, 172.68.189.105 Cf-Ray: 5d11f7ba09b08522-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Upgrade-Insecure-Requests: 1 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.83 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.9 Sec-Fetch-Site: cross-site Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://www.google.com/ Accept-Language: en-US,en;q=0.9 Cookie: __cfduid=d40d91cd3bcc6d0b26e6153ee87bca1041599749270; _ga=GA1.2.1080008113.1599749271; _gid=GA1.2.2098970943.1599749271; hide_intro=True; page_size=100; views=47 Cf-Request-Id: 051f1928430000852277a56200000001 Cf-Connecting-Ip: 4.28.60.194 Cdn-Loop: cloudflare X-Forwarded-Host: pennydreadfulmagic.com X-Forwarded-Server: pennydreadfulmagic.com Connection: Keep-Alive ```