Database Indexing ka Funda
Understand how Database Indexing works with Fullstackgada
More Diagrams
Follow for More
🚀 Search Performance ka Demo
Bhide ki Pareshani: Website pe search bahut slow! Bina index ke database har record ko check karta hai.
📚 Index Types ka Explorer
Jethalal ka Gyaan: Alag-alag needs ke liye alag-alag indexes!
📖 Book Index ka Analogy
📑
Kitab ke peechhe Index page hota hai - Topic A page 50 pe, Topic B page 75 pe
⚡
Database Index bhi waisa hi - Record A row 1000 mein, Record B row 2500 mein
🎯
Direct jump kar jate hain, har page nahi dekhna padta!
🛒 Products Table aur Index Management
🛒 Products Table (Sample Data)
| ProductID | 🔍 Name (Indexed) | 🔍 Category (Indexed) | Price | Description |
|---|---|---|---|---|
| 1 | Soap | Personal Care | ₹45 | Premium bathing soap with natural ingredients |
| 2 | Shampoo | Personal Care | ₹120 | Anti-dandruff shampoo for healthy hair |
| 3 | Rice | Grocery | ₹80 | Basmati rice 1kg pack premium quality |
| 4 | Oil | Grocery | ₹150 | Mustard oil cold pressed organic |
| 5 | Papad | Snacks | ₹25 | Crispy papad made from lentils |
📋 Current Indexes
| Index Name | Table | Columns | Type | Status |
|---|---|---|---|---|
| idx_product_name | Products | Name | Single Column | ✅ Active |
| idx_category_price | Products | Category, Price | Composite | ✅ Active |
| idx_product_id | Products | ProductID | Unique | ✅ Active |
Index ke Fayde:
🔍 Name Index: Product search jaldi
🧩 Composite Index: Category + Price filter tez
⭐ Unique Index: Koi duplicate ProductIDs nahi
🔍 Name Index: Product search jaldi
🧩 Composite Index: Category + Price filter tez
⭐ Unique Index: Koi duplicate ProductIDs nahi
💻 Index Management ke SQL Commands
/* Index Banaye */
-- Single Column Index
CREATE INDEX idx_product_name ON Products(Name);
-- Composite Index
CREATE INDEX idx_category_price ON Products(Category, Price);
-- Unique Index
CREATE UNIQUE INDEX idx_product_id ON Products(ProductID);
-- Full-Text Index
CREATE FULLTEXT INDEX idx_description ON Products(Description);
/* Index Manage Karo */
-- Sab Indexes Dikhao
SHOW INDEX FROM Products;
-- Index Hatao
DROP INDEX idx_product_name ON Products;
-- Index Usage Check Karo
EXPLAIN SELECT * FROM Products WHERE Name = 'Soap';
📌 Indexing ke Best Practices
| Aspect | ✅ Accha Practice | ❌ Galat Practice | Jethalal ki Salah |
|---|---|---|---|
| Column Selection | Jo columns baar-baar search ya filter hote hain | Har column pe index | Jahan baar-baar search, wahan index lagao |
| Index Count | Soch-samajhkar indexing | Bahut saare indexes | Dhyaan se lagao, warna INSERT slow ho jayega |
| Composite Indexes | Sabse selective column pehle | Random column order | Jo zyada unique, woh pehle rakho |
| Maintenance | Monitor karo aur unused hatao | Banake bhool jao | Check karte raho kaun se kaam aa rahe hain |