Database.If i define primary key as student_id, course_id, why i use only WHERE student_id = 1, for example, and how index of primary key works here ?

🧱 1. Understanding Composite Primary Key Index

When you define:

PRIMARY KEY (student_id, course_id)

The database creates a composite index on (student_id, course_id) — this is a B-tree index in most databases.

Order matters here:

• It’s indexed like:
  (student_id → course_id)

That means:

• The index can be used efficiently if you filter by:
  • student_id
  • or student_id AND course_id
• But not efficiently if you filter by:
  • only course_id

📊 Visualizing the Index:

Think of the index like a phonebook sorted by:

(student_id, course_id)

📘 Indexed like this:

student_id	course_id
1	101
1	102
2	101
2	103

So:

• WHERE student_id = 1 → ✅ fast lookup (index helps)
• WHERE student_id = 1 AND course_id = 101 → ✅ even faster (precise match)
• WHERE course_id = 101 → ❌ not efficient (index ignored)

🧪 2. Why You Can Use Only student_id in WHERE Clause

Because the index starts with student_id, the database can:

• Navigate directly to all rows where student_id = 1
• Then scan the inner values (course_id) if needed

This is called a left-prefix match — using the first column(s) of a composite index.

---

🧠 Rule of Thumb:

A composite index on (A, B) can be used by queries filtering:

• A ✅
• A AND B ✅
• B alone ❌ (index not used unless a separate index on B exists)

---

🛠 Recommendation:

If you often query by just course_id, create a separate index:

CREATE INDEX idx_course ON StudentCourses(course_id);