When to Use / Not Use Indexes

When TO Use Indexes

Columns in WHERE clause (high selectivity)
Columns in JOIN conditions
Columns in ORDER BY (avoids sort in memory)
Columns in GROUP BY
High cardinality columns (email, user_id — many unique values)
Range queries on sorted data (created_at BETWEEN ...)

When NOT to Use Indexes

Small tables (<10k rows) — full scan is faster; DB planner ignores index anyway
Low cardinality columns (gender, status) — returns too many rows
Heavy write tables — every INSERT/UPDATE/DELETE must update ALL indexes
Columns rarely used in queries — wastes storage and slows writes
LIKE with leading wildcard: WHERE name LIKE '%john%' — cannot use B+ tree
Functions on indexed columns: WHERE YEAR(created_at) = 2024 — DB can't traverse B+ tree on transformed values

The Core Tradeoff

More indexes → SELECT faster   ✅
             → INSERT slower   ❌
             → UPDATE slower   ❌
             → DELETE slower   ❌
             → More storage    ❌

Composite Index Tips — Leftmost Prefix Rule

sql

-- Index on (last_name, first_name)
SELECT * WHERE last_name = 'Smith'                          -- Uses index
SELECT * WHERE last_name = 'Smith' AND first_name = 'John'  -- Uses index
SELECT * WHERE first_name = 'John'                          -- CANNOT use index

Practical Decision Framework

Is the table large? (>10k rows) → NO: skip index. Is the column high cardinality? → NO: skip. Is the column in WHERE/JOIN/ORDER BY frequently? → NO: skip. Is the table write-heavy? → YES: think twice. All above passed → add the index.

When to Use / Not Use Indexes ​

When TO Use Indexes ​

When NOT to Use Indexes ​

The Core Tradeoff ​