Index selection strategy 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 filter data by certain columns.

When you join tables on specific columns.

When you sort data by a column frequently.

When you want to avoid scanning the whole table for queries.

Syntax

MySQL

CREATE INDEX index_name ON table_name (column1, column2, ...);

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

Choosing the right columns for indexes improves performance.

Examples

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

MySQL

CREATE INDEX idx_lastname ON employees (last_name);

Creates a multi-column index to speed up queries filtering by last name and date of birth.

MySQL

CREATE INDEX idx_name_dob ON employees (last_name, date_of_birth);

Creates a unique index to ensure emails are unique and speed up email lookups.

MySQL

CREATE UNIQUE INDEX idx_email ON users (email);

Sample Program

This example creates a table, adds an index on the department column, inserts some data, and shows how the database uses the index to find employees in Sales.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50)
);

CREATE INDEX idx_department ON employees (department);

INSERT INTO employees (id, first_name, last_name, department) VALUES
(1, 'Alice', 'Smith', 'Sales'),
(2, 'Bob', 'Brown', 'HR'),
(3, 'Carol', 'Davis', 'Sales');

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

Important Notes

Indexes speed up read queries but can slow down inserts and updates because the index must be updated too.

Choose columns that are often used in WHERE, JOIN, or ORDER BY clauses for indexing.

Avoid indexing columns with many duplicate values, like boolean flags, as they may not improve performance.

Summary

Indexes help the database find data faster by creating shortcuts.

Create indexes on columns used often in searches or joins.

Too many indexes can slow down data changes, so choose wisely.