What is a composite index and when would you use it?

Short answer: A composite index is an index that is created on multiple columns.

Explain a bit more

It is used when a query filters or sorts based on more than one column. Composite indexes are especially useful when queries frequently involve conditions on multiple columns. Example Use Case: If a query filters by both last_name and first_name, creating a composite index on (last_name, first_name) will speed up the query. Example of creating a composite index: CREATE INDEX idx_lastname_firstname ON employees(last_name, first_name);

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.