MySQL Indexes Complete Guide

Indexes in MySQL

Indexes dramatically speed up data retrieval. MySQL supports several index types for different needs.

Creating Indexes

-- Single column
CREATE INDEX idx_email ON users(email);

-- Composite (multiple columns)
CREATE INDEX idx_name_age ON users(last_name, first_name);

-- Unique
CREATE UNIQUE INDEX idx_username ON users(username);

Index Types

Type	Use
PRIMARY KEY	Unique row identifier
UNIQUE	No duplicates allowed
INDEX	Standard speed-up
FULLTEXT	Text search

Composite Index Order Matters

INDEX(last_name, first_name)
-- ✅ Used: WHERE last_name = 'X'
-- ✅ Used: WHERE last_name = 'X' AND first_name = 'Y'
-- ❌ Not used: WHERE first_name = 'Y' alone

Managing Indexes

SHOW INDEX FROM users;
DROP INDEX idx_email ON users;

FAQs

Can too many indexes hurt?

Yes — they slow writes and use space. Index only what you query. More in our MySQL section.

Does PRIMARY KEY create an index?

Yes — automatically.