🔹 What is Indexing?
Indexing is a data structure technique used in databases to speed up data retrieval.
Instead of scanning the entire table for every query (which is slow), the database uses an index to quickly locate the required rows.
🔹 Analogy:
Think of a book:
Without an index, you’d flip through every page to find a topic.
With an index at the back, you jump straight to the right page.That’s exactly what database indexing does — it jumps to the right place fast.
🔹 Why Do We Need Indexes?
-
Improve read performance (e.g., SELECT queries)
-
Support faster searching, sorting, filtering
-
Help in joining tables efficiently
-
Used in WHERE, ORDER BY, JOIN, GROUP BY, etc.
🔹 How It Works (Conceptually):
When you index a column (say username):
-
The DB creates a sorted data structure (like a B-Tree or Hash Table) with mappings from values to row locations.
-
Queries using that column don’t scan the full table — they lookup directly via the index.
🔹 Types of Indexes:
| Index Type | When to Use |
|---|---|
| Primary Index | Automatically created on the primary key |
| Secondary Index | Created manually on other columns |
| Unique Index | Enforces uniqueness on a column |
| Composite Index | Combines two or more columns |
| Full-text Index | For searching within large text columns (e.g., articles, logs) |
| Hash Index | Fast exact-match lookups (used in memory-heavy engines like Redis) |
| Clustered Index | The actual table is ordered according to the index (only one per table) |
| Non-clustered Index | Separate structure that references the actual data (can be many) |
🔹 Downsides of Indexing:
-
Slower writes: INSERT, UPDATE, DELETE operations become heavier because index also needs updating
-
More storage: Indexes take extra disk space
-
Bad indexes = no performance gain + unnecessary cost
🔹 When Should You Create Indexes?
✅ Create indexes on:
-
Columns used in WHERE clauses frequently
-
Columns used in JOINs
-
Columns in ORDER BY or GROUP BY
❌ Avoid indexing:
-
Columns that are rarely filtered
-
Columns with high write frequency
-
Columns with low selectivity (e.g., boolean flags)
🔹 SQL Syntax (Example):
CREATE INDEX idx_username ON users(username);To remove:
DROP INDEX idx_username;