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

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

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

✔ 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 id for 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 EXPLAIN to 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 EXPLAIN and 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.