Why should you care?

Dashboard counting orders per customer by id only — index (customer_id, order_id) covers SELECT without touching row data.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE INDEX idx_orders_cover ON orders(customer_id, order_id, total_inr);
EXPLAIN SELECT customer_id, order_id, total_inr
FROM orders
WHERE customer_id BETWEEN 10 AND 20;

What happened?

• All three columns live in index leaf pages.
• Engine satisfies query from index alone — fewer random I/O trips to clustered index.

Practice next

1. Run EXPLAIN — look for Using index in Extra.
2. Compare rows read with vs without covering columns in index.
3. Avoid SELECT * — breaks covering benefit.
4. Drop total_inr from index — Using index may disappear.
5. COUNT(*) covered by (customer_id) only query.

Remember

Include SELECT/WHERE columns in index when hot. Using index in EXPLAIN = index-only scan. Trade storage for read speed.

DataFlow analytics rollups

Hourly job aggregates from covering index — CPU drops 40% vs full row fetch.

Outcome: Replica lag shrinks during report window.