Late returns dashboard

Boss · indexing, sorting, leftmost-prefix, lateral, 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

across 50 stores, list the 15 most recent rentals whose status is 'late', across all stores,

newest rental_date first. If two share a rental_date, the higher id comes first.

Output the columns (id, store_id, rental_date).

Expected columns

id, store_id, rental_date

Hints

Hint 1 — the missing prefix

idx_store_status_date is (store_id, status, rental_date). Without store_id, rental_date isn't sorted across the whole index, only inside each (store_id, status) group. EXPLAIN the obvious query: MySQL skip-scans every late rental (about 12,000) and sorts them.

Hint 2 — per store, it's easy

For one store, 'Late at store 7' showed the newest late rentals are one short backward walk. The overall top 15 must be among each store's own top 15. List the stores cheaply with SELECT DISTINCT store_id FROM rentals (a loose index scan), then take each store's top 15 with a LATERAL derived table.

Hint 3 — check the inner plan

Inside LATERAL, store_id comes from the outer row, so MySQL won't treat it as a constant when matching ORDER BY to the index. If EXPLAIN shows 'Using filesort' on the inner query, name the whole prefix: ORDER BY r.store_id DESC, r.status DESC, r.rental_date DESC, r.id DESC.