Why indexes speed up queries in MySQL

Introduction

Indexes help the database find data faster, like a shortcut in a book's index that points directly to the page you want.

When you want to quickly find a specific record in a large table.

When you often search for data using certain columns.

When you want to speed up sorting or filtering results.

When you want to improve performance of JOIN operations between tables.

Syntax

MySQL

CREATE INDEX index_name ON table_name (column_name);

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

Indexes make read operations faster but can slow down writes like INSERT or UPDATE.

Examples

Creates an index on the last_name column of the employees table to speed up searches by last name.

MySQL

CREATE INDEX idx_lastname ON employees (last_name);

Creates a combined index on name and age columns to speed up queries filtering by both.

MySQL

CREATE INDEX idx_name_age ON users (name, age);

Sample Program

This example creates a books table, inserts some data, adds an index on the author column, and then shows how the query uses the index to find books by Alice faster.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(50)
);

INSERT INTO books VALUES
(1, 'Learn SQL', 'Alice'),
(2, 'Data Basics', 'Bob'),
(3, 'SQL Speed', 'Alice');

CREATE INDEX idx_author ON books (author);

EXPLAIN SELECT * FROM books WHERE author = 'Alice';

Important Notes

Indexes work like a phone book index, letting the database jump directly to the data instead of scanning every row.

Too many indexes can slow down data changes because each index must be updated.

Use indexes on columns you search or join on frequently.

Summary

Indexes speed up data searches by creating shortcuts to data locations.

They are especially useful on large tables and frequent search columns.

Creating indexes improves read speed but may slow down writes.