Load more

Core · indexing, pagination, sorting

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

with about 10 million rows, an app lists the bugs of the reporter token = 'token4242' sorted by

reported_at, then by id, 10 at a time. The last row on screen is the bug with id = 6805335,

reported_at = '2020-02-02'. Return the next 10 rows of the list.

Output the columns (id, reported_at).

Expected columns

id, reported_at

Hints

Hint 1 — ties on the cursor

Look at token4242's rows on 2020-02-02: several bugs share that date. reported_at > '2020-02-02' skips the ones after id 6805335, and >= repeats the ones already shown. The cursor needs both columns.

Hint 2 — the hidden column

A secondary index stores the primary key after its own columns, so index_bugs_on_category_and_token_and_reported_at is really sorted by (category, token, reported_at, id). Pin category and token, and ORDER BY reported_at, id is exactly the index order.

Hint 3 — spell out the cursor

Write 'after (D, N)' as reported_at > D OR (reported_at = D AND id > N). Compare EXPLAIN and rows read with the shorter row-constructor form (reported_at, id) > (D, N).