Late at store 7

Warm-up · indexing, sorting, limit

Given 2 tables, customers(id, name, country) and

rentals(id, store_id, customer_id, inventory_id, status, rental_date, amount) with 3 million rows,

list the 10 most recent rentals of store 7 whose status is 'late', newest rental_date first.

If two share a rental_date, the higher id comes first. Output the columns (id, rental_date).

Expected columns

id, rental_date

Hints

Hint 1 — one index, two jobs

SHOW INDEX FROM rentals: idx_store_status_date is (store_id, status, rental_date). With store_id and status pinned by equality, its entries are already sorted by rental_date, so the same index can find the rows and return them in order.

Hint 2 — same direction

An index can be read forwards or backwards, but not both at once. Every secondary index also ends with the primary key, so rental_date DESC, id DESC is one backward walk. Mixing DESC and ASC forces MySQL to collect every matching row and sort them.