SQL Indexes

SQL Indexes

An index is like a book's table of contents: it helps the database find rows faster.

Why use indexes?

Without an index, the database scans every row to find matches. An index lets it jump straight to the right data. This makes WHERE, JOIN, and ORDER BY much faster on large tables.

Create an index

CREATE INDEX idx_lastname
ON Persons (LastName);

A unique index

CREATE UNIQUE INDEX idx_email
ON Persons (Email);

No two rows may have the same Email.

Drop an index

DROP INDEX idx_lastname ON Persons;   -- MySQL
DROP INDEX idx_lastname;              -- SQL Server

The trade-off

Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the index must be kept updated. Index only the columns you actually search on.

When indexes are automatic

Primary keys and UNIQUE columns are indexed automatically by most databases.

TL;DR

• Indexes make searches faster.
• Use them on columns in WHERE / JOIN / ORDER BY.
• They slow down writes, so don't over-index.
• Primary keys are indexed automatically.