When to index

Choose which columns to index.

When to index?

Indexes go on frequently queried columns, not every column. Low-repetition values are good for indexing.

CREATE INDEX idx_email ON customers (email);

Email is ideal for indexing as it is unique.

The index idx_email is created.

Primary keys and UNIQUE columns are automatically indexed. They need no extra index.

Add indexes to columns used often in WHERE, JOIN and ORDER BY that have many distinct values; low-variety columns (e.g. gender) gain little.

Add an index on email (frequently searched).

Show example solution

CREATE INDEX idx_email ON customers (email);

Add CREATE INDEX

CREATE ____ idx_email ON customers (email);

Show answer

CREATE INDEX idx_email ON customers (email);