Single column index in SQL

Introduction

A single column index helps the database find data faster by organizing one column's values. It works like an index in a book, so you don't have to look through every page.

When you often search or filter data by one specific column, like a username or ID.

When you want to speed up queries that sort results by a single column.

When you want to quickly check if a value exists in a column.

When you want to improve performance of joins using one column.

When you want to enforce uniqueness on a single column (using unique index).

Syntax

SQL

CREATE INDEX index_name ON table_name (column_name);

The index_name is a name you choose to identify the index.

The column_name is the single column you want to index.

Examples

This creates an index on the name column in the customers table to speed up searches by customer name.

SQL

CREATE INDEX idx_customer_name ON customers (name);

This creates a unique index on the email column in the users table to ensure no duplicate emails.

SQL

CREATE UNIQUE INDEX idx_email_unique ON users (email);

Sample Program

This example creates a table employees and adds a single column index on the department column. Then it inserts some data and queries employees in the 'Sales' department. The index helps the database find these rows faster.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50)
);

CREATE INDEX idx_department ON employees (department);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Sales');

SELECT * FROM employees WHERE department = 'Sales';

Important Notes

Indexes speed up read queries but can slow down inserts, updates, and deletes because the index must be updated too.

Use indexes only on columns you query often to avoid unnecessary overhead.

Summary

A single column index speeds up queries on one column.

Create it with CREATE INDEX on the column you want.

It helps find data faster but adds some cost when changing data.