MySQL — Indexes

What is an Index?

An index is a data structure that speeds up row lookups, similar to a book's index.

CREATE INDEX idx_email ON customers(email);

Types of Indexes

Type	Purpose
PRIMARY KEY	Unique identifier, one per table
UNIQUE	Ensures no duplicate values
INDEX	Standard speedup for lookups
FULLTEXT	Full-text search on text columns
COMPOSITE	Index on multiple columns

Create Index

CREATE INDEX idx_name ON customers(last_name, first_name);

Unique Index

CREATE UNIQUE INDEX idx_email ON users(email);

Composite Index

CREATE INDEX idx_dept_salary ON employees(department, salary);

View Indexes

SHOW INDEX FROM customers;

Drop Index

DROP INDEX idx_email ON customers;

When to Create Indexes

• Columns in WHERE clauses
• Columns in JOIN conditions
• Columns in ORDER BY
• High-cardinality columns (many unique values)

When NOT to Index

• Small tables
• Columns with few unique values
• Frequently updated columns
• Tables with heavy INSERT/UPDATE workload

Mini Practice

Write SQL code that:

1. Creates a single-column index
2. Creates a composite index
3. Creates a unique index
4. Views existing indexes on a table

Up Next

Continue with Constraints — enforcing data integrity rules.