SQL Indexes

An index is a database object that speeds up the retrieval of rows from a table. Without an index, the database must scan every row in a table from top to bottom to find the matching records — a process called a full table scan. An index provides a shortcut, similar to how a book's index at the back helps find a topic without reading every page.

Think of a phone book. Without it, finding a person would mean reading every name from page 1 to the end. A phone book is an index — it sorts names alphabetically so the search jumps directly to the right section.

How Indexes Work

When an index is created on a column, the database builds a separate, sorted data structure (typically a B-tree) that maps each value in the column to its location in the table. When a query filters by that column, the database uses the index to go directly to the matching rows instead of scanning the entire table.

Types of Indexes

Creating an Index

Syntax

CREATE INDEX index_name
ON table_name (column_name);

Example 1: Index on City Column

CREATE INDEX idx_city
ON Students (City);

After this index is created, queries that filter by City will run significantly faster — especially on large tables with millions of rows.

-- This query now benefits from the index
SELECT * FROM Students WHERE City = 'Delhi';

Example 2: Unique Index

CREATE UNIQUE INDEX idx_email
ON Students (Email);

This creates an index on the Email column and also enforces that no two students can have the same email address.

Example 3: Composite Index (Index on Multiple Columns)

CREATE INDEX idx_city_course
ON Students (City, Course);

This index is useful for queries that filter by both City and Course together:

SELECT * FROM Students WHERE City = 'Delhi' AND Course = 'Science';

The composite index works best when the query includes the leading column (City in this case) in the filter.

Viewing Indexes on a Table

SHOW INDEX FROM Students;

This lists all indexes defined on the Students table, including the primary key index.

Dropping an Index

DROP INDEX idx_city ON Students;

This removes the index. The data in the table is not affected — only the index structure is removed.

When Are Indexes Created Automatically?

SQL automatically creates indexes in two situations:

• When a column is defined as PRIMARY KEY
• When a column is defined with the UNIQUE constraint

Manual indexes are created by the developer on columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.

When to Use Indexes

The Trade-Off: Speed vs Storage

Indexes are not free. They come with trade-offs:

• Read speed improves — SELECT queries run faster.
• Write speed decreases slightly — INSERT, UPDATE, and DELETE operations take a little longer because the index must also be updated whenever data changes.
• Storage space increases — Indexes occupy additional disk space.

Therefore, indexes should be created thoughtfully — only on columns that genuinely benefit from them, not on every column.

Full-Text Index (For Text Search)

CREATE FULLTEXT INDEX idx_description
ON Products (Description);

Full-text indexes are used with MATCH ... AGAINST syntax for keyword-based text searches within large text fields.

Key Points to Remember

• Indexes speed up SELECT queries but slow down INSERT, UPDATE, and DELETE slightly.
• PRIMARY KEY and UNIQUE columns automatically get indexes.
• Create indexes on columns frequently used in WHERE, JOIN ON, and ORDER BY clauses.
• Do not over-index — every index requires storage and maintenance overhead.
• Composite indexes are most effective when the query filters use the leading columns.