## What is Database Indexing?
An index is a database structure that speeds up data retrieval by creating a fast lookup mechanism for specific columns. Instead of scanning every row to find what you need, the database uses the index to jump directly to the relevant data.
Think of it like a book index. Instead of reading every page to find mentions of "database," you check the index at the back, which tells you exactly which pages contain that word.
## The Problem Indexes Solve
Without an index, finding a user by email means checking every single row in the users table. If you have 10 million users, that takes seconds or minutes.
With an index on the email column, the database finds the user instantly, even with 10 million rows.
## How Indexes Work
When you create an index on a column, the database builds a separate data structure (usually a B-tree) that organizes values for fast searching.
```sql
CREATE INDEX idx_email ON users(email);
```
Now searching by email is lightning fast:
```sql
SELECT * FROM users WHERE email = "john@example.com";
```
The database uses the index instead of scanning the entire table.
## Types of Indexes
**Single Column Index**: Index on one column (most common).
```sql
CREATE INDEX idx_last_name ON users(last_name);
```
**Composite Index**: Index on multiple columns together.
```sql
CREATE INDEX idx_name ON users(first_name, last_name);
```
**Unique Index**: Ensures no duplicate values (often used with primary keys).
```sql
CREATE UNIQUE INDEX idx_username ON users(username);
```
**Full-Text Index**: Optimized for searching text content.
```sql
CREATE FULLTEXT INDEX idx_content ON articles(content);
```
## The Trade-Off
Indexes make reads faster but writes slower.
**Faster Reads**: Queries using indexed columns execute almost instantly.
**Slower Writes**: Every INSERT, UPDATE, or DELETE must also update all relevant indexes.
**More Storage**: Indexes consume disk space (sometimes significant amounts).
This is why you do not index every column - only the ones you frequently search or filter by.
## When to Create Indexes
Index columns that appear in:
- WHERE clauses frequently
- JOIN conditions
- ORDER BY clauses
- Columns used for sorting or filtering
Common examples:
- Email addresses (for login lookups)
- User IDs (for foreign key joins)
- Created dates (for time-based queries)
- Status fields (for filtering active/inactive records)
## When NOT to Index
**Small Tables**: If a table has 100 rows, indexes add overhead without meaningful speed gains.
**Frequently Updated Columns**: If a column changes constantly, maintaining the index becomes expensive.
**Low Cardinality Columns**: Columns with few unique values (like boolean fields) benefit less from indexing.
## Real-World Impact
An unindexed query on a large table might take 30 seconds. After adding the right index, the same query runs in 0.01 seconds.
E-commerce sites index product IDs, categories, and prices. Social media platforms index user IDs, timestamps, and hashtags. The right indexes mean the difference between fast, responsive apps and slow, frustrating ones.
## Monitoring Index Usage
Most databases provide tools to see which indexes are being used and which are wasted:
```sql
-- PostgreSQL
SELECT * FROM pg_stat_user_indexes;
-- MySQL
SHOW INDEX FROM users;
```
Remove unused indexes - they slow down writes without providing benefits.
## Best Practices
**Start Simple**: Index primary keys and foreign keys first.
**Monitor Queries**: Use database query analyzers to find slow queries.
**Add Indexes Strategically**: Index based on real query patterns, not guesses.
**Test Impact**: Measure query performance before and after adding indexes.
**Regular Maintenance**: Rebuild fragmented indexes periodically.
## The Bottom Line
Indexes are one of the most powerful tools for database optimization. A few well-placed indexes can transform a slow application into a fast one.
Learn to identify which columns need indexing by analyzing your queries. This skill is essential for any developer working with databases.
