**Read vs Write Trade-off:**

No indexes:

- SELECT: Full table scan (slow)

- INSERT: Fast (just write row)

- Storage: 1x table size

1 index:

- SELECT: Index scan (fast)

- INSERT: Write row + update 1 index (6x slower)

- Storage: 1.2x table size

10 indexes:

- SELECT: Very fast (many options)

- INSERT: Write row + update 10 indexes (51x slower)

- Storage: 5x table size

Rule: Balance based on read/write ratio.

- Read-heavy (90% reads): More indexes OK

- Write-heavy (50% writes): Fewer indexes

- Balanced: Strategic indexes only

**Index Selectivity:**

High selectivity (good for indexing):

- email: 1M unique values in 1M rows (100%)

- user_id: 100K unique in 1M rows (10%)

- Index very effective

Low selectivity (poor for indexing):

- gender: 2 values in 1M rows (0.0002%)

- status: 5 values in 1M rows (0.0005%)

- Index not effective

Rule: Index columns with >1% selectivity.

**Composite Index Column Order:**

Query: WHERE city = 'Seattle' AND country = 'US' AND age > 25

Selectivity:

- city: 10,000 unique (high)

- country: 50 unique (low)

- age: 100 unique (low)

Wrong order: (country, city, age)

- Scans all US rows first (2M rows)

- Then filters by city

- Slow

Right order: (city, country, age)

- Scans Seattle rows first (100 rows)

- Then filters by country