Why should you care?

Login by email needs idx_customers_email — without it MySQL scans entire customers table every auth.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE INDEX idx_customers_email ON customers(email);
EXPLAIN SELECT customer_id, full_name
FROM customers
WHERE email = 'priya@dataflow.in';

What happened?

• Index on email lets optimizer seek matching entries, then fetch rows by customer_id PK.
• type=ref in EXPLAIN with key idx_customers_email shows index use.

Practice next

1. ADD email column if missing; populate sample rows.
2. EXPLAIN before index — type ALL.
3. CREATE INDEX; EXPLAIN again.
4. DROP INDEX and measure slow query log entry.
5. Unique index on email prevents duplicate accounts.

Remember

Secondary indexes speed selective WHERE/JOIN. Each index costs write overhead. EXPLAIN confirms index pick.

DataFlow login path

Auth service queries by email 50k times/min — idx_customers_email required.

Outcome: Login P99 stays under 20ms.