Database.Middle.What is a composite index? When would you use one?

Great question, Stanley! Let’s break down composite indexes clearly — what they are, how they work, and when to use them.

🧱 What Is a Composite Index?

A composite index (aka multi-column index) is an index that covers two or more columns of a table.

Instead of indexing just one column, like:

You create a composite index like:

CREATE INDEX idx_email_name ON users(email, name);

Now the index contains both email and name, stored in sorted order by (email, name).

🧠 How It Works

Think of it as a sorted list like this:

(email1, name1)
(email1, name2)
(email2, name1)
(email2, name3)

That means:

• It’s efficient to look up by email
• Also efficient to look up by email AND name
• But not efficient to look up by just name ❌

🔍 When Can You Use a Composite Index?

✅ Use when queries filter or sort by multiple columns together.

Example:

SELECT * FROM users WHERE email = 's@example.com' AND name = 'Stanley';

✅ Composite index on (email, name) will be used efficiently.

📊 Prefix Matching Rule

With a composite index on (A, B, C):

Query Condition	Can Use Index?
WHERE A = ?	✅ Yes
WHERE A = ? AND B = ?	✅ Yes
WHERE B = ?	❌ No
WHERE A = ? AND C = ?	✅ (partial)
ORDER BY A, B	✅ Yes

You can only use the leftmost prefix efficiently.

✅ When to Use Composite Indexes

Scenario	Index to Use
Search users by email and name	(email, name)
Find orders by customer_id and date	(customer_id, order_date)
Filter by country, city	(country, city)
Sort by created_at DESC, id DESC	(created_at DESC, id DESC)

❌ When Not to Use One

• If you always filter by name only, a composite index on (email, name) won’t help.
• If queries use columns independently, it may be better to create separate single-column indexes.

🧠 Summary

Feature	Composite Index
Indexes multiple columns	✅ Yes
Left-to-right usage rule	✅ Must match from left column
Best for	Queries filtering/sorting by multiple columns
Tip	Don’t create blindly — match your queries