How to Create an Index in SQL

Use an index when queries need faster lookups, filtering, joins, or sorting on frequently used columns. Indexes are one of the most effective ways to improve SQL read performance.

What you’ll build or solve

You’ll learn how to create an index in SQL and choose the right columns for it. You’ll also know when indexes can hurt write performance.

When this approach works best

This approach is the right choice when the same columns are repeatedly used in WHERE, JOIN, ORDER BY, or uniqueness checks.

Common real-world scenarios include:

• Email lookups
• Order date filters
• Join keys
• Product SKU search
• Dashboard sorting

This is a bad idea when the table is tiny or the indexed column changes constantly.

Prerequisites

You only need:

• A table with queryable columns
• Permission to modify schema
• Awareness of slow query patterns

Step-by-step instructions

Step 1: Create the index on the frequently queried column

The basic syntax is:

CREATE INDEX idx_users_email
ON users(email);

This speeds up email-based lookups.

Indexes work especially well for join keys.

CREATE INDEX idx_orders_user_id
ON orders(user_id);

This can significantly improve join performance.

You can also create multi-column indexes.

CREATE INDEX idx_orders_status_date
ON orders(status, created_at);

What to look for:

• Great for repeated filters and joins
• Speeds up reads
• Multi-column order matters
• Too many indexes slow writes
• Best on selective columns

Examples you can copy

Email lookup

CREATE INDEX idx_customers_email
ON customers(email);

SKU search

CREATE INDEX idx_products_sku
ON products(sku);

Sorting by date

CREATE INDEX idx_orders_created_at
ON orders(created_at);

Common mistakes and how to fix them

Mistake 1: Indexing low-cardinality columns

What the reader might do:

Index a boolean column like is_active.

Why it breaks: very low selectivity may provide little benefit.

Corrected approach:

Index columns with strong filtering power.

Mistake 2: Creating too many overlapping indexes

What the reader might do:

Add separate indexes for (status) and (status, created_at) unnecessarily.

Why it breaks: writes slow down and storage grows.

Corrected approach:

Consolidate into the most useful composite index.

Mistake 3: Ignoring column order in composite indexes

What the reader might do:

ON orders(created_at, status)

Why it breaks: this may not help WHERE status = ... queries as expected.

Corrected approach:

Put the most selective leading filter first.

Troubleshooting

If performance does not improve, confirm the query planner uses the index.

If writes slow down, reduce unnecessary indexes.

If joins are still slow, index both sides of the key relationship.

If sorting queries remain expensive, include the sort column in the index.

Quick recap

• Use indexes to speed up reads
• Best for filters, joins, and sorting
• Composite index order matters
• Too many indexes slow writes
• Choose selective columns