Find the title

Core · indexing, leftmost-prefix

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

with about 10 million rows, find the title of the bug with token = 'token660' and

reported_at = '2020-08-30'. Output the column (title).

Expected columns

title

Hints

Hint 1 — which index

Run SHOW INDEX FROM bugs: Seq_in_index gives the column order of each index. Compare that order with the columns in your WHERE, then EXPLAIN your query. key = NULL means MySQL cannot use the index for it.

Hint 2 — the leftmost prefix

A B-tree index can only be searched from its leftmost column. Look at the rows: category is the same value everywhere, so adding category = 'bug' (or category IN (...) with every category) does not change the answer but makes the index usable.