When indexes help and when they hurt in SQL

Introduction

Indexes make searching in a database faster, but they can slow down adding or changing data.

When you want to quickly find records in a large table, like searching for a customer by ID.

When you often sort or filter data by a specific column, like showing products by price.

When you join tables on certain columns frequently, to speed up the matching process.

When you rarely update or delete data in a table, so index maintenance is minimal.

When you have a small table, indexes might not help and can slow things down.

Syntax

SQL

CREATE INDEX index_name ON table_name (column_name);

Indexes are created on one or more columns to speed up queries.

Too many indexes can slow down data changes like INSERT, UPDATE, DELETE.

Examples

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

SQL

CREATE INDEX idx_customer_name ON customers (name);

Creates an index on 'order_date' to quickly find orders by date.

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 shows creating a table, inserting data, adding an index on the 'category' column, and then querying by category. The index helps the query run faster.

SQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  category VARCHAR(20),
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Pen', 'Stationery', 1.20),
(2, 'Notebook', 'Stationery', 2.50),
(3, 'Coffee Mug', 'Kitchen', 5.00);

-- Create index to speed up searches by category
CREATE INDEX idx_category ON products (category);

-- Query to find all stationery products
SELECT * FROM products WHERE category = 'Stationery';

Important Notes

Indexes speed up SELECT queries but slow down INSERT, UPDATE, and DELETE because the index must be updated too.

Use indexes on columns that are often searched or used in JOINs.

Avoid indexing columns that have many repeated values or are rarely used in queries.

Summary

Indexes make searching data faster but slow down data changes.

Create indexes on columns you search or join on often.

Too many indexes can hurt performance, so add them carefully.