Chapter 11 — Indexes ⭐⭐⭐
Assume this table:
employees
+--------+--------+---------+
| id | name | salary |
+--------+--------+---------+
| 1 | Rahul | 50000 |
| 2 | Aman | 70000 |
| ... 10 Million Rows ... |
Q1. Suppose your company has 10 million employees, and your manager says:
"Find employee whose id = 8765432."
Answer (Without Index)
SELECT *
FROM employees
WHERE id = 8765432;
Database checks:
Row 1 ❌
Row 2 ❌
Row 3 ❌
...
Row 8765432 ✅
This is called Full Table Scan.
Slow.
Create Index
CREATE INDEX idx_employee_id
ON employees(id);
Now the same query:
SELECT *
FROM employees
WHERE id = 8765432;
Database jumps almost directly to the record.
Fast.
Concepts Covered
Index
Full Table Scan
Faster Searching
B-Tree (internally)
Q2. Suppose your manager says:
"Should we create indexes on every column?"
Answer
❌ No.
Bad idea.
CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_email ON employees(email);
CREATE INDEX idx_phone ON employees(phone);
...
Problems
INSERT becomes slower
UPDATE becomes slower
DELETE becomes slower
More storage required
Indexes speed up reading, but slow down writing.
Q3. Suppose users search employees by both department and salary.
Answer
Instead of
CREATE INDEX idx_department
ON employees(department);
CREATE INDEX idx_salary
ON employees(salary);
Use Composite Index
CREATE INDEX idx_dept_salary
ON employees(department, salary);
Useful query
SELECT *
FROM employees
WHERE department='IT'
AND salary > 50000;
Q4. Suppose the interviewer asks:
"When should we create an index?"
Answer
Create indexes on columns frequently used in
WHERE
JOIN
ORDER BY
GROUP BY
Avoid indexing
Small tables
Frequently updated columns
Columns rarely searched
Chapter 12 — EXPLAIN
Assume
SELECT *
FROM employees
WHERE name='Rahul';
Q1. Suppose your query is taking 8 seconds.
How will you find the reason?
Answer
Use
EXPLAIN
SELECT *
FROM employees
WHERE name='Rahul';
Output (simplified)
type = ALL
rows = 10000000
key = NULL
Meaning
Database scanned every row.
Q2. Suppose after creating an index, you run EXPLAIN again.
Answer
CREATE INDEX idx_name
ON employees(name);
EXPLAIN
SELECT *
FROM employees
WHERE name='Rahul';
Output
type = ref
key = idx_name
rows = 1
Meaning
Database used the index.
Very fast.
Q3. Suppose EXPLAIN shows
key = NULL
What does it mean?
Answer
No index was used.
Possible reasons
No index exists
Wrong query
Function on indexed column
Database optimizer chose table scan
Q4. Suppose interviewer asks:
"Why do developers use EXPLAIN?"
Answer
To know
Which index is used
Which table is scanned
Estimated rows
Join order
Query cost
It helps optimize slow queries.
Chapter 13 — Transactions
Assume Rahul transfers ₹1000 to Priya.
Current Balance
Rahul 5000
Priya 3000
Q1. Suppose money is deducted from Rahul but the server crashes before adding it to Priya.
What should happen?
Answer
Use Transaction.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
COMMIT;
Either both succeed
OR
None happen.
Q2. Suppose second query fails.
Answer
START TRANSACTION;
UPDATE accounts
SET balance = balance -1000
WHERE id=1;
-- Error
ROLLBACK;
Output
Rahul still has
5000
Money isn't lost.
Q3. Suppose interviewer asks:
"Difference between COMMIT and ROLLBACK?"
Answer
COMMIT
Save permanently
ROLLBACK
Undo everything since transaction started
Q4. Suppose interviewer asks:
"What are ACID properties?"
Answer
A → Atomicity
Everything or nothing.
C → Consistency
Database remains valid.
I → Isolation
Transactions don't interfere.
D → Durability
Committed data survives crashes.
Chapter 14 — Full Text Search
Assume
articles
id
title
content
Q1. Suppose users search
Machine Learning
Should you use
WHERE title LIKE '%Machine%'
?
Answer
No.
Better
MATCH(title, content)
AGAINST('Machine Learning');
Much faster.
Designed for searching text.
Q2. Suppose users search
AI Engineer
Answer
SELECT *
FROM articles
WHERE MATCH(title,content)
AGAINST('AI Engineer');
Output
Only relevant articles.
Q3. Suppose interviewer asks:
"LIKE vs Full Text Search?"
Answer
LIKE
✔ Simple
✔ Small tables
❌ Slow on large text
Full Text
✔ Fast
✔ Ranking
✔ Natural language search
✔ Millions of rows
Q4. Suppose you want to enable Full Text Search.
Answer
CREATE FULLTEXT INDEX idx_article
ON articles(title,content);
Now
MATCH...
AGAINST...
works efficiently.
Chapter 15 — Pagination
Assume
Products
1 million rows
Q1. Suppose your website displays 20 products per page.
How will you show Page 1?
Answer
SELECT *
FROM products
LIMIT 20;
Output
Rows 1-20
Q2. Suppose user clicks Page 2.
Answer
SELECT *
FROM products
LIMIT 20 OFFSET 20;
Output
Rows 21-40
Q3. Suppose user clicks Page 5.
Answer
SELECT *
FROM products
LIMIT 20 OFFSET 80;
Formula
OFFSET
(page-1) × page_size
Q4. Suppose interviewer asks:
"Why is OFFSET pagination slow?"
Answer
For
OFFSET 900000
Database still skips
900000 rows.
Better approach
Keyset Pagination
SELECT *
FROM products
WHERE id > 500
LIMIT 20;
Much faster.
Chapter 16 — Basic Scaling Concepts
Q1. Suppose 10 lakh users visit your website today.
Database becomes slow.
What will you do first?
Answer
Use Cache.
Instead of
User
↓
Database
Use
User
↓
Cache
↓
Database
Frequently requested data comes from cache.
Database load reduces.
Popular tools
Redis
Memcached
Q2. Suppose thousands of users are only reading data.
Answer
Use Read Replica.
Writes
↓
Primary Database
↓
Replica 1
Replica 2
Replica 3
Read queries go to replicas.
Write queries go to primary.
Q3. Suppose every post shows
Likes = 12,45,678
Should database count likes every time?
Answer
No.
Maintain
likes_count
inside Posts table.
Whenever someone likes
likes_count +=1
This is called
Denormalized Counter
Much faster.
Q4. Suppose interviewer asks:
"Cache vs Replica vs Denormalized Counter"
Answer
| Concept | Purpose |
|---|---|
| Cache | Avoid repeated database queries |
| Replica | Handle many read requests |
| Denormalized Counter | Avoid expensive COUNT() queries |
Chapter 17 — Final Integration (Putting It All Together)
Assume you're building Amazon.
Q1. Suppose customer searches:
"Wireless Mouse"
What database concepts are used?
Answer
Full Text Search
↓
Index
↓
LIMIT 20
SQL
SELECT *
FROM products
WHERE MATCH(name, description)
AGAINST('Wireless Mouse')
LIMIT 20;
Q2. Suppose customer opens page 6 of products.
What concepts are involved?
Answer
SELECT *
FROM products
ORDER BY id
LIMIT 20 OFFSET 100;
Concepts used:
ORDER BY
LIMIT
OFFSET (Pagination)
Index on
idfor faster sorting
Q3. Suppose customer places an order.
What happens?
Answer
START TRANSACTION;
UPDATE products
SET stock = stock - 1
WHERE id = 10;
INSERT INTO orders(...);
UPDATE users
SET total_orders = total_orders + 1
WHERE id = 5;
COMMIT;
Concepts involved:
Transaction
COMMIT
Primary Key
Foreign Key
Index
Atomicity
Q4. Suppose interviewer asks:
"A query is slow in production. What is your step-by-step approach?"
Answer
Run
EXPLAINto inspect the execution plan.Check whether the query is using an appropriate index.
Add or optimize indexes if necessary.
Rewrite inefficient joins or filters.
Cache frequently requested data.
Use read replicas if the workload is read-heavy.
Replace expensive
COUNT()operations with denormalized counters where appropriate.Re-run
EXPLAINand measure performance again.
🎯 At this point, you've covered the core SQL concepts used in real-world applications:
Database Basics
Tables & Data Types
CRUD
WHERE
ORDER BY
LIMIT
Joins
GROUP BY
Aggregate Functions
Primary & Foreign Keys
Database Design
Indexes
EXPLAIN
Transactions
Full-Text Search
Pagination
Basic Scaling Concepts
This progression takes you from writing simple queries to understanding how production databases are designed, optimized, and scaled.