SQL Indexes
Speeding Up Data Retrieval

Introduction

Imagine you’re trying to find one student’s report from thousands of records. Scanning everything line by line would be slow. What if your database had a smart “table of contents” to jump straight to the relevant entry? That’s what an index does in SQL.

What is an Index in SQL?

A SQL INDEX is a performance optimization structure. It allows the database to find and retrieve data faster — similar to how a book index helps locate chapters quickly.

When Should You Use Indexes?

• On columns frequently used in WHERE clauses
• On columns used in JOIN or ORDER BY
• For large datasets where performance matters

Sample Table – students

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A', 'Delhi'),
(2, 'Diya Iyer', '9B', 'Chennai'),
(3, 'Sneha Patil', '10A', 'Pune'),
(4, 'Mehul Agarwal', '9B', 'Delhi'),
(5, 'Saira Bano', '8C', 'Hyderabad');

1. Create an Index on City

Let’s create an index on the city column since we often search students by city:

CREATE INDEX idx_city
ON students(city);

2. Query with Index Usage

Now, this query becomes faster with the index:

SELECT name, class
FROM students
WHERE city = 'Delhi';

name           | class
----------------+-------
Aarav Sharma   | 10A
Mehul Agarwal  | 9B

3. Composite Index (Multiple Columns)

Create an index on both class and city for combined filters:

CREATE INDEX idx_class_city
ON students(class, city);

This helps with queries like:

SELECT name
FROM students
WHERE class = '9B' AND city = 'Delhi';

name
----------
Mehul Agarwal

4. Unique Index

Indexes can also enforce uniqueness (aside from primary keys):

CREATE UNIQUE INDEX idx_unique_name
ON students(name);

This prevents duplicate names (useful if names must be unique in your context).

5. Drop an Index

DROP INDEX idx_city;

Important Notes

• Indexes improve SELECT performance, but slow down INSERT/UPDATE/DELETE a bit.
• Use only where needed — don’t blindly index every column.
• Use EXPLAIN (in MySQL/PostgreSQL) to see if your index is being used.

Real-World School Use Case

The school admin needs to quickly list students by city and class — indexing those columns speeds up daily reporting, especially when thousands of students are enrolled.

Summary

SQL indexes are the silent speed-boosters of your database. By indexing the right columns — like student names, cities, or class identifiers — you can dramatically improve query performance. Just like a smart librarian, indexes help SQL find answers fast.

What’s Next?

Next, we’ll learn about SQL Transactions — ensuring data consistency when multiple queries run together.