Creating indexes in MySQL

Introduction

Indexes help the database find data faster, like a shortcut in a book's table of contents.

When you want to speed up searches on a large table.

When you often look up data by a specific column, like a username or email.

When you join tables on certain columns frequently.

When you want to enforce uniqueness on a column to avoid duplicates.

Syntax

MySQL

CREATE INDEX index_name ON table_name (column_name);

-- For unique values:
CREATE UNIQUE INDEX index_name ON table_name (column_name);

You can create an index on one or more columns by listing them inside the parentheses separated by commas.

Unique indexes ensure no two rows have the same value in the indexed column(s).

Examples

Creates an index named 'idx_lastname' on the 'last_name' column of the 'employees' table.

MySQL

CREATE INDEX idx_lastname ON employees (last_name);

Creates a unique index on the 'email' column to prevent duplicate emails in the 'users' table.

MySQL

CREATE UNIQUE INDEX idx_email ON users (email);

Creates an index on two columns, 'last_name' and 'date_of_birth', to speed up queries filtering by both.

MySQL

CREATE INDEX idx_name_dob ON persons (last_name, date_of_birth);

Sample Program

This creates a 'books' table and adds an index on the 'author' column. The EXPLAIN statement shows how MySQL uses the index to find rows faster.

MySQL

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

CREATE INDEX idx_author ON books (author);

-- Query to test the index
EXPLAIN SELECT * FROM books WHERE author = 'Jane Austen';

Important Notes

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

Use indexes on columns that you search or join on often, but avoid adding too many indexes.

You can drop an index with: DROP INDEX index_name ON table_name;

Summary

Indexes make searching data faster by creating shortcuts.

Create indexes on columns you use often in searches or joins.

Unique indexes prevent duplicate values in a column.