Index design is about choosing which indexes to create based on your actual query patterns, data characteristics, and performance requirements. The goal: make reads fast without making writes too slow.

Think of it like organizing a library. Bad: Index every word in every book (too many indexes, slow to add new books). Good: Index by author, title, and subject (strategic indexes for common searches).

**Index Design Process:**

1. **Analyze Query Patterns**

- Which queries run most frequently?

- Which queries are slowest?

- What columns appear in WHERE, JOIN, ORDER BY?

- What's the data distribution (cardinality)?

2. **Choose Index Type**

- Single-column: One column, simple queries

- Composite: Multiple columns, complex queries

- Covering: Includes all SELECT columns

- Partial: Filtered subset of rows

- Unique: Enforces uniqueness

3. **Determine Column Order**

- Most selective column first

- Equality before range conditions

- Match query WHERE clause order

4. **Balance Trade-offs**

- Read performance vs write overhead

- Storage cost vs query speed

- Index maintenance vs query coverage

**Index Types and When to Use:**

**1. Single-Column Index**

When:

- Queries filter on one column

- Column has high cardinality (many unique values)

- Simple equality or range queries

Example:

CREATE INDEX idx_email ON users(email);

-- For: WHERE email = 'user@example.com'

Pros:

- Simple to create and maintain

- Works for any query using that column

- Low overhead

Cons:

- Doesn't help multi-column queries

- May not be selective enough

**2. Composite Index (Multi-Column)**

When: