Indexes

Explanation

An index is a behind-the-scenes data structure (usually a B-tree) that lets the database find rows by a column value without scanning the whole table — similar to how the index of a book lets you jump to a topic instead of reading every page.

When you filter or join on an indexed column, the planner can seek directly to the matching rows. The cost is storage and slower writes, because the index must be kept in sync as data changes.

Syntax

CREATE INDEX idx_employees_department
  ON employees (department_id);
 
-- Multi-column (composite) index:
CREATE INDEX idx_orders_customer
  ON orders (customer_id, order_date);
 
-- Remove an index you no longer need:
DROP INDEX idx_employees_department;

Interactive Example

Run a filter on department_id. Without an index this scans all rows; with an index the planner seeks straight to matches. Try filtering and sorting to feel the shape of an indexed access path.

Common Mistakes

• Indexing every column. Each index slows writes and wastes space; only index what you query.
• Indexing low-selectivity columns. A boolean is_active rarely benefits from its own index because it matches half the table.
• Wrapping indexed columns in functions. WHERE YEAR(hire_date) = 2020 can't use a simple index on hire_date; filter on the raw column instead.

Best Practices

• Index foreign keys — joins use them constantly.
• Put the most selective column first in a composite index.
• Periodically drop unused indexes; monitor which queries are slow.

Practice Question

Imagine a orders table queried constantly by customer_id then order_date. Write the CREATE INDEX statement for the best composite index to support that access pattern.

Summary

Indexes are B-tree structures that accelerate reads on filtered/joined columns at the cost of write overhead and storage. Index foreign keys and frequent WHERE/ORDER BY columns, and avoid indexing everything.

FAQ