Latest rentals from Egypt

Core · indexing, sorting, joins, join-order, 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 made by customers whose country is 'Egypt', newest rental_date

first. If two share a rental_date, order them by inventory_id descending, then customer_id

descending. Output the columns (rental_date, inventory_id, name).

Expected columns

rental_date, inventory_id, name

Hints

Hint 1 — who drives the join

EXPLAIN the obvious join. MySQL starts from customers (country isn't indexed, so it reads all 50,000), fetches every Egyptian customer's rentals through idx_customer, and sorts the result. An index can only sort a join when every ORDER BY column belongs to the first table in the plan.

Hint 2 — walk rentals newest first

SHOW INDEX FROM rentals: the unique key rental_date is (rental_date, inventory_id, customer_id) — exactly the requested order. Read it backwards and check each rental's customer by primary key; about 2% of customers are Egyptian, so the 10th match comes after a few hundred rentals.

Hint 3 — tell MySQL the order

STRAIGHT_JOIN joins tables in the order you write them: FROM rentals r STRAIGHT_JOIN customers c ON .... Keep the ORDER BY on rentals columns that match the index; adding r.id breaks it, because id sits after customer_id in that index.