An index is a special lookup data structure that a database uses to speed up the retrieval of rows based on certain column values.
Think of an index like the index of a book — it helps you find content quickly, without scanning every page.
🎯 What Are Indexes Used For?
- Speed up queries that use
WHERE,JOIN,ORDER BY,GROUP BY - Help databases find rows without scanning the entire table
- Enforce uniqueness in columns (e.g., primary keys, unique constraints)
🧱 Types of Indexes (commonly used)
| Type | Description |
|---|---|
| B-Tree Index | Default in most RDBMS; good for range and equality queries |
| Hash Index | Fast for equality (=), not for ranges |
| Unique Index | Ensures values in the column are unique |
| Composite Index | Index over multiple columns ((A, B)) |
| Full-Text Index | Used for text search in large text fields |
✅ Advantages of Indexes
| Advantage | Benefit |
|---|---|
| 🚀 Faster SELECT queries | Especially on large datasets |
| 🔗 Faster JOINs | Speeds up joining related tables |
| 🔍 Quick lookup/search | Ideal for searching by key or common columns |
| 🧱 Enforces constraints | E.g., UNIQUE, PRIMARY KEY |
| 📈 Improves sorting/grouping | Helps with ORDER BY, GROUP BY clauses |
❌ Disadvantages of Indexes
| Disadvantage | Problem |
|---|---|
🐌 Slower INSERT, UPDATE, DELETE | Indexes must be updated along with the data |
| 💾 Extra storage | Indexes consume disk space |
| 🧠 Over-indexing hurts | Too many indexes can confuse the query planner or cause overhead |
| ❓ Wrong index choice | May lead to worse performance than no index |
🛠️ Example:
-- Create an index on the email column
CREATE INDEX idx_email ON Users(email);
-- Now this query is fast
SELECT * FROM Users WHERE email = 'alice@example.com';
🧠 Tip: When to Use Indexes
Use indexes when:
- You frequently query or filter on a column
- A column is used in joins or sorting
- The table has many rows (thousands or millions)
Avoid or limit indexes when:
- You write-heavy tables (frequent updates/inserts)
- You index columns with low uniqueness (e.g., gender, boolean)