Lesson 11 � Performance

QUERIES KO
FAST BANAO.

Bina index ke 1 million rows mein se data nikalne mein 2 second lagta hai � index se 0.001 second. Index database ka search optimization hai, bilkul library ka index card jaisa. Production databases mein yeh mandatory hai.

WHY: Index kyun zaroori hai?

Socho ek library mein 10 lakh kitabein hain. Agar tumharo paas index card nahi hai toh har kitab ek ek karke dekhni padegi � full table scan. Lekin agar alphabetically sorted index hai toh seedha "M" section mein jaake "MySQL" dhundh sakte ho � index scan. Yahi SQL indexes karte hain: data dhundhne ka shortcuts banana.

HOW: Index banana aur use karna

Index banana simple hai � CREATE INDEX statement se. Lekin dhyan rakho: index read operations (SELECT) ko fast karta hai, lekin write operations (INSERT, UPDATE, DELETE) ko thoda slow karta hai � kyunki index bhi update hota hai har change ke saath.

1. Basic INDEX banao

Students table ke name column pe index banao:

-- Name column pe index banao
CREATE INDEX idx_name ON students(name);

-- Ab yeh query fast hogi
SELECT * FROM students WHERE name = 'Aman';

2. UNIQUE INDEX banao

Email column pe unique index � duplicate emails allowed nahi hongi:

-- Email pe unique index
CREATE UNIQUE INDEX idx_email ON students(email);

-- Duplicate email daalne par error aayega
INSERT INTO students (id, name, email, course, marks)
VALUES (4, 'Neha', 'aman@test.com', 'SQL', 88.00);
-- ERROR: Duplicate entry for key 'idx_email'

3. COMPOSITE INDEX banao

Course aur marks dono columns pe ek saath index:

-- Composite index: course + marks
CREATE INDEX idx_course_marks ON students(course, marks);

-- Yeh query fast hogi
SELECT * FROM students WHERE course = 'Python' AND marks > 85;

-- Partial composite bhi kaam karega (sirf course wali query)
SELECT * FROM students WHERE course = 'SQL';

4. Index drop karo

Agar index ki zaroorat nahi rahi:

-- Index hatao
DROP INDEX idx_name ON students;

-- MySQL mein syntax
ALTER TABLE students DROP INDEX idx_name;

5. Check karo kaunse indexes hain

Table pe kaunse indexes lage hain:

-- Saare indexes dikhao
SHOW INDEX FROM students;

-- PostgreSQL mein
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'students';

6. Query performance check karo � EXPLAIN

EXPLAIN se dekh sakte ho query index use kar rahi hai ya full table scan ho raha hai:

-- Performance check karo
EXPLAIN SELECT * FROM students WHERE name = 'Aman';

-- Without index: type = ALL (Full table scan � slow)
-- With index: type = ref (Index scan � fast!)
-- Rows scanned: 1000000 vs 1

Performance comparison

Real numbers se samjho index ka power:

-- Scenario: 1,00,000 rows in students table

-- WITHOUT INDEX
SELECT * FROM students WHERE name = 'Aman';
-- Time: ~2.0 seconds
-- Method: Full table scan (1,00,000 rows check kiye)

-- WITH INDEX ON name
SELECT * FROM students WHERE name = 'Aman';
-- Time: ~0.001 seconds
-- Method: Index scan (sirf 1 row check kiya)
-- Speed improvement: 2000x faster!

Index types ka quick reference

-- 1. Single column index
CREATE INDEX idx_name ON students(name);

-- 2. Unique index
CREATE UNIQUE INDEX idx_email ON students(email);

-- 3. Composite index (multiple columns)
CREATE INDEX idx_course_marks ON students(course, marks);

-- 4. Drop index
DROP INDEX idx_name ON students;

-- 5. Check indexes
SHOW INDEX FROM students;

-- 6. Performance check
EXPLAIN SELECT * FROM students WHERE name = 'Aman';

Try it: Indexes banao aur performance check karo

Neeche ke editor mein indexes create karo aur EXPLAIN se dekho query kaise fast hoti hai. Pehle bina index ke query run karo, phir index banao aur phir se same query chalao.

Common beginner mistakes

• Har column pe index lagana: Zyada indexes = slow writes. Sirf frequently queried columns pe index lagao � har column pe mat lagao.
• Composite index mein order na samajhna: idx(course, marks) � sirf course wali queries fast hongi, sirf marks wali nahi. Column order WHERE clause ke order se match hona chahiye.
• EXPLAIN na check karna: Index bana diya lekin pata nahi kaam kar raha hai ya nahi. Hamesha EXPLAIN se verify karo.
• Small tables pe index lagana: Agar table mein sirf 100 rows hain toh index ki zaroorat nahi � full table scan bhi fast hai. 10,000+ rows se index meaningful hota hai.
• UNIQUE INDEX vs PRIMARY KEY confuse karna: PRIMARY KEY automatically unique hota hai � alag se UNIQUE INDEX lagane ki zaroorat nahi. UNIQUE INDEX un columns ke liye hai jo primary key nahi hain lekin unique hone chahiye.