Indexing

An Index helps the database locate rows efficiently, like a book index, but adds overhead for writes and storage.

CREATE INDEX idx_employee_department
ON employees(department_id);

Costs: storage, insert/update/delete overhead, maintenance.

Query Optimization

Indexes, Execution plans, Statistics, Join strategy, Filtering, Avoid SELECT *, Partitioning, Rewriting

EXPLAIN

EXPLAIN shows the database execution plan, revealing how it will scan, join, and filter data to run your query.

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

Shows how DB will execute query.

Partitioning

Divide large table into smaller partitions (Range/List/Hash). Example orders by year: 2023/2024/2025

Sharding

Distribute data across nodes. Application → Shard 1/2/3