What is database indexing and when to use it?

Question

Accepted Answer

**Indexing** — creating data structure for faster lookups. **When** — frequent queries on specific columns.

**How Indexes Work:**
- B-tree index (most common).
- Speed up SELECT queries.
- Slow down INSERT/UPDATE/DELETE.
- Use on WHERE, JOIN, ORDER BY columns.

```
Without Index:
  SELECT * FROM users WHERE email = 'a@b.com'
  (Full table scan: 1M rows)

With Index:
  CREATE INDEX idx_email ON users(email)
  SELECT * FROM users WHERE email = 'a@b.com'
  (Index scan: ~3-4 operations)
```

**Types:**
- **B-tree** — range queries, equality.
- **Hash** — equality only.
- **Full-text** — text search.
- **Composite** — multiple columns.

**Best Practices:**
- Index frequently queried columns.
- Don't over-index.
- Use EXPLAIN to analyze queries.
- Consider covering indexes.

Systems Analyst: What is database indexing and when to use it?