Count em bugs

Boss · indexing, primary-key, binary-search

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

with about 10 million rows, find how many bugs were created on 2019-03-01 or later.

Output the column (count).

Expected columns

count

Hints

Hint 1 — what is indexed

Run SHOW INDEX FROM bugs and check whether created_at appears in any index. Then EXPLAIN your WHERE created_at query: key = NULL with rows near 10M means a full scan. Which index could narrow a range here instead?

Hint 2 — look at the rows

Look at the first rows: created_at grows as id grows. Rows are inserted in order, so 'created on or after 2019-03-01' is the same as 'id at or after some boundary id'.

Hint 3 — find the boundary

Binary search with Run: SELECT created_at FROM bugs WHERE id >= <mid> LIMIT 1 is a single primary-key dive. Halve the id range until you find the first id created on 2019-03-01, then COUNT(*) WHERE id >= that id.