Everyone's latest report

Boss · indexing, top-n-per-group, loose-index-scan, sorting

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

with about 10 million rows, find for every reporter (token) the bug they reported most recently:

the one with the latest reported_at, and if several share that date, the one with the highest id.

Output the columns (token, id).

Expected columns

token, id

Hints

Hint 1 — list the reporters cheaply

There are about 30,000 tokens and 10M rows. As in 'What are the categories of bugs we have?', SELECT DISTINCT category, token can jump through the index from one token to the next instead of reading every row.

Hint 2 — one dive per token

For a single token, the newest bug is the last index entry under (category, token): the index is sorted by (category, token, reported_at, id). A correlated subquery with ORDER BY ... DESC LIMIT 1 can read just that one entry, once per token.

Hint 3 — check EXPLAIN for filesort

If EXPLAIN shows 'Using filesort' on the inner query, MySQL reads all ~330 rows of each token. The inner WHERE compares to the outer row, not to constants, so MySQL won't drop category and token from the sort. Put the whole index in the ORDER BY — category DESC, token DESC, reported_at DESC, id DESC — and look for 'Backward index scan'.