Skip to content
Zeni Study
Share
Explore

Day 6 — Indexes, Constraints & How Databases Think

⁠

🟢 Day 6 — Indexes, Constraints & How Databases Think

🎯 Day 6 Outcome

By the end of Day 6, the student should be able to:
Explain why indexes exist
Decide what to index and what not to
Use constraints to protect data
Answer DB design questions verbally
Avoid common performance mistakes
Interviewers don’t expect you to tune databases. They expect you to not do stupid things.
⁠

1️⃣ Why Queries Become Slow (Teaching Script)

Ask the student:
“How does the database find a row?”
Without index:
It scans every row
Called full table scan
With index:
Uses a data structure (like sorted map)
Directly jumps to the row
Indexes exist to avoid scanning everything.
⁠

2️⃣ What Is an Index? (Simple Explanation)

An index is a separate data structure that helps the database find rows faster.
Example:
Phone book → index on name
Book → index at the end
⁠

3️⃣ Example Without Index

SELECT * FROM users WHERE email = 'a@gmail.com';
Database:
Checks every row
Slow if table is large
⁠

4️⃣ Creating an Index

CREATE INDEX idx_users_email ON users(email);
Now:
Database jumps directly to matching rows
Much faster for read queries
⁠

5️⃣ What Columns Should Be Indexed? (Interview Gold)

Good Candidates for Index

Primary keys (auto-indexed)
Foreign keys
Columns used in:
WHERE
JOIN
ORDER BY

Bad Candidates

Columns with very few unique values (e.g. gender)
Columns rarely queried
Small tables (index overhead > benefit)
⁠

6️⃣ Trade-off of Indexes (Must Know)

Advantage
Cost
Faster reads
Slower writes
Faster joins
More memory
Faster filters
Index maintenance
There are no rows in this table
⁠
❗ Don’t index everything blindly.
⁠

7️⃣ Constraints — Guardrails for Data

Constraints stop bad data from entering DB.
⁠

PRIMARY KEY

Unique
Not null
user_id INT PRIMARY KEY
⁠

UNIQUE

email VARCHAR(100) UNIQUE
Prevents duplicate emails
⁠

NOT NULL

name VARCHAR(100) NOT NULL
⁠

FOREIGN KEY

FOREIGN KEY (user_id) REFERENCES users(user_id)
Ensures relationship correctness
⁠

8️⃣ What Interviewers Ask (Very Common)

Q: Why not store everything in one table?

A:
Data duplication
Inconsistency
Hard updates
⁠

Q: Why index foreign keys?

A:
Faster JOINs
Faster lookups
⁠

Q: Why not index all columns?

A:
Slower inserts/updates
Wasted memory
⁠

9️⃣ Schema Design Thinking (Practice)

Design DB for Blog System

Entities:
Users
Posts
Comments
⁠

Suggested Schema

users(user_id PK, name, email UNIQUE)
posts(post_id PK, user_id FK, title, content)
comments(comment_id PK, post_id FK, user_id FK, text)
⁠

Index Suggestions

users(user_id)
posts(user_id)
comments(post_id)
⁠

🔟 Practice Problems (Think, Not Just Write)

Problem 1 — Indexing

Which columns should be indexed in:
orders(order_id, user_id, created_at, status)
⁠

Problem 2 — Constraints

Which constraints would you add for:
users table
orders table
⁠

Problem 3 — Verbal

Answer:
Why indexes speed up SELECT?
Why indexes slow down INSERT?
What happens if index is missing?
⁠

🔍 HINTS

Index = fast search, slow write
Constraint = data safety
Think usage patterns, not syntax
⁠

✅ Sample Answers

Problem 1 — Indexing

Index:
order_id (PK)
user_id (FK)
created_at (if queried often)
⁠

Problem 2 — Constraints

users:
PRIMARY KEY (user_id)
UNIQUE (email)
NOT NULL (name)
orders:
PRIMARY KEY (order_id)
FOREIGN KEY (user_id)
⁠

🧠 Reflection Questions

When does index hurt performance?
Why is primary key always indexed?
Gallery
 
Want to print your doc?
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.