Why indexes matter in SQL

Introduction

Indexes help databases find information faster, just like an index in a book helps you find a topic quickly.

When you want to speed up searching for specific data in a large table.

When you often filter or sort data by certain columns.

When you want to improve performance of queries that join tables on specific columns.

When you have a large database and want to reduce the time it takes to get results.

When you want to avoid scanning the entire table for every query.

Syntax

SQL

CREATE INDEX index_name ON table_name (column_name);

An index is created on one or more columns of a table.

The index name should be unique within the database.

Examples

Creates an index on the 'name' column of the 'customers' table to speed up searches by customer name.

SQL

CREATE INDEX idx_customer_name ON customers (name);

Creates an index on the 'order_date' column of the 'orders' table to speed up queries filtering by date.

SQL

CREATE INDEX idx_order_date ON orders (order_date);

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

SQL

CREATE INDEX idx_product_category ON products (category, price);

Sample Program

This example creates a table 'employees', inserts some data, creates an index on the 'department' column, and then shows the query plan for searching employees in the 'Sales' department. The index helps the database find these employees faster.

SQL

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

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

CREATE INDEX idx_department ON employees (department);

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';

Important Notes

Indexes speed up data retrieval but can slow down data insertion and updates because the index must be updated too.

Not every column needs an index; use indexes on columns you search or sort by often.

Too many indexes can use extra storage and reduce performance on writes.

Summary

Indexes make searching data faster by creating a quick lookup structure.

Use indexes on columns you often filter or join on.

Indexes improve read speed but can slow down writes.