CREATE INDEX syntax in SQL

Introduction

Creating an index helps the database find data faster, like a shortcut in a book's table of contents.

When you want to speed up searches on a large table.

When you often look up data by a specific column, like customer ID.

When you want to improve performance of queries with WHERE conditions.

When sorting results by a certain column frequently.

When joining tables on a specific column to make it faster.

Syntax

SQL

CREATE INDEX index_name ON table_name (column1, column2, ...);

The index_name is a name you give to the index to identify it.

You can create an index on one or more columns to speed up queries.

Examples

Creates an index named idx_customer_name on the 'name' column of the 'customers' table.

SQL

CREATE INDEX idx_customer_name ON customers (name);

Creates an index on the 'order_date' column to speed up date-based searches.

SQL

CREATE INDEX idx_order_date ON orders (order_date);

Creates a multi-column index on 'category' and 'price' to speed up queries filtering by both.

SQL

CREATE INDEX idx_product_category_price ON products (category, price);

Sample Program

This example creates a table 'employees' and then creates an index on the 'department' column to speed up searches by department.

SQL

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

CREATE INDEX idx_department ON employees (department);

-- Now searching by department will be faster
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.

Choose columns for indexes that you use often in search conditions.

Too many indexes can waste space and reduce performance.

Summary

CREATE INDEX makes searching data faster by creating shortcuts.

You specify the table and columns to index.

Use indexes wisely to balance speed and storage.