Late or lost

Core · indexing, sorting, union, 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 12 whose status is 'late' or 'lost', newest rental_date

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

Output the columns (id, status, rental_date).

Expected columns

id, status, rental_date

Hints

Hint 1 — what IN does to the order

Run the obvious query with status IN ('late', 'lost') and EXPLAIN it: 'Using filesort'. The index is sorted by (store_id, status, rental_date), so the late rows and the lost rows are two separate sorted runs. Across both, rental_date is not in order, so MySQL reads all of them and sorts.

Hint 2 — one run at a time

With a single status pinned, the index order is the ORDER BY, as in 'Late at store 7'. The newest 10 of the combined list must be among the newest 10 late plus the newest 10 lost.

Hint 3 — merge

Wrap each half in parentheses with its own ORDER BY ... LIMIT 10, UNION ALL them in a derived table, then ORDER BY rental_date DESC, id DESC LIMIT 10 outside. The final sort only sees 20 rows.