SQL Indexes (The Book Index) 📖¶

Prerequisites: SELECT Statement, Table Management

Mentor's Note: Imagine a 1,000-page textbook. If you want to find "SQL Joins", would you read every page from 1 to 1,000? No! You go to the index at the back, find the page number, and flip straight to it. An Index does the exact same thing for your database! 💡

🌟 The Scenario: The Search Challenge 🔍¶

Full Table Scan: Looking at every single row in a table of 1 million users to find "Vishnu". (Slow! 🐢)
Indexed Search: Using a pre-sorted list of names to jump directly to the row ID. (Instant! ⚡)

📖 1. The Two Main Types¶

A. Clustered Index (The Book Itself)¶

Logic: The actual data rows are sorted and stored in this order.
Analogy: A Phone Book sorted by Last Name.
Rule: You can only have ONE per table (because data can only be physically sorted one way!).
Default: In most systems, the Primary Key is automatically a Clustered Index.

B. Non-Clustered Index (The Index at the Back)¶

Logic: A separate structure that points to the data rows.
Analogy: The "Index" at the back of a textbook.
Rule: You can have many per table.

💻 2. Basic Syntax¶

-- Create an index on 'email' to speed up logins
CREATE INDEX idx_user_email
ON users (email);

-- Unique Index (Prevents duplicates and speeds up search)
CREATE UNIQUE INDEX idx_roll_no
ON students (roll_no);

-- Remove an index
DROP INDEX idx_user_email;

📊 Comparison Table¶

Feature	Clustered Index	Non-Clustered Index
Physical Sorting	✅ Yes	❌ No
Storage	Uses the table itself	Requires extra disk space
Speed	Fastest for ranges	Fast for specific lookups
Quantity	Max 1	Multiple (999+)

⚠️ The "Tax" of Indexing¶

While indexes make Reading (SELECT) faster, they make Writing (INSERT/UPDATE/DELETE) slower. - Why? Because every time you add a row, the database has to update all the indexes too!

💡 Pro Tip¶

"Only index columns that you use frequently in WHERE or JOIN clauses. Don't index every column, or your 'Insert' speed will crawl to a halt!"