Skip to content
Explain why indexes existDecide what to index and what not toUse constraints to protect dataAnswer DB design questions verballyAvoid common performance mistakes
It scans every rowCalled full table scanUses a data structure (like sorted map)Directly jumps to the row
Phone book → index on nameBook → index at the end
Checks every rowSlow if table is large
Database jumps directly to matching rowsMuch faster for read queries
Primary keys (auto-indexed)Foreign keysColumns used in:WHEREJOINORDER BY
Columns with very few unique values (e.g. gender)Columns rarely queriedSmall tables (index overhead > benefit)
UniqueNot null
Prevents duplicate emails
Ensures relationship correctness
Data duplicationInconsistencyHard updates
Faster JOINsFaster lookups
Slower inserts/updatesWasted memory
UsersPostsComments
users(user_id)posts(user_id)comments(post_id)
orders(order_id, user_id, created_at, status)
users tableorders table
Why indexes speed up SELECT?Why indexes slow down INSERT?What happens if index is missing?
Index = fast search, slow writeConstraint = data safetyThink usage patterns, not syntax
order_id (PK)user_id (FK)created_at (if queried often)
PRIMARY KEY (user_id)UNIQUE (email)NOT NULL (name)PRIMARY KEY (order_id)FOREIGN KEY (user_id)
When does index hurt performance?Why is primary key always indexed?What’s worse: missing index or bad schema?How would you explain index to a non-tech person?
Zeni Study
- Pages
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:
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:
With index:
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:
3️⃣ Example Without Index
SELECT * FROM users WHERE email = 'a@gmail.com';
Database:
4️⃣ Creating an Index
CREATE INDEX idx_users_email ON users(email);
Now:
5️⃣ What Columns Should Be Indexed? (Interview Gold)
Good Candidates for Index
Bad Candidates
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
user_id INT PRIMARY KEY
UNIQUE
email VARCHAR(100) UNIQUE
NOT NULL
name VARCHAR(100) NOT NULL
FOREIGN KEY
FOREIGN KEY (user_id) REFERENCES users(user_id)
8️⃣ What Interviewers Ask (Very Common)
Q: Why not store everything in one table?
A:
Q: Why index foreign keys?
A:
Q: Why not index all columns?
A:
9️⃣ Schema Design Thinking (Practice)
Design DB for Blog System
Entities:
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
🔟 Practice Problems (Think, Not Just Write)
Problem 1 — Indexing
Which columns should be indexed in:
Problem 2 — Constraints
Which constraints would you add for:
Problem 3 — Verbal
Answer:
🔍 HINTS
✅ Sample Answers
Problem 1 — Indexing
Index:
Problem 2 — Constraints
users:
orders:
🧠 Reflection Questions
⭐ Day 6 Golden Rule
Indexes optimize queries. Constraints protect data. Both are required for real systems.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.