A reporter's 2020

Core · indexing, sargable, range

Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at)

with about 10 million rows, count how many bugs the reporter with token = 'token777' reported

during 2020. Output the column (count).

Expected columns

count

Hints

Hint 1 — reach the token

SHOW INDEX FROM bugs: the composite index starts with category, then token, then reported_at. As in 'Find the title', the index is only usable once its leftmost column is pinned.

Hint 2 — keep the column bare

YEAR(reported_at) = 2020 is correct, but MySQL can't search the index for the result of a function. It reads every row of the token and computes YEAR() on each. How would you write 'during 2020' as a range on reported_at itself?