Introduction
Indexes are the single most impactful performance optimization in MySQL. A well-indexed table can answer queries in microseconds; the same query on an unindexed table might take seconds or minutes. This guide covers every MySQL index type, when to use each, and how to analyze index effectiveness.
How Indexes Work
MySQL indexes are separate data structures that maintain a sorted copy of one or more columns, allowing the database to find rows without scanning the entire table.
Without an index:
-- Full table scan: checks every row
SELECT * FROM users WHERE email = '[email protected]';
-- Time: O(n) — proportional to table size
With an index on email:
-- Index lookup: jumps directly to matching rows
SELECT * FROM users WHERE email = '[email protected]';
-- Time: O(log n) — B-tree traversal
B-Tree Index (Default)
The default index type in InnoDB. Stores data in a balanced tree structure, supporting equality, range, and prefix queries.
-- Create a regular B-tree index
CREATE INDEX idx_users_name ON users (name);
-- Create during table definition
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(200),
age INT,
INDEX idx_name (name),
INDEX idx_age (age)
);
-- Add index to existing table
ALTER TABLE users ADD INDEX idx_email (email);
What B-Tree Indexes Support
-- Equality
WHERE name = 'Alice'
-- Range
WHERE age BETWEEN 20 AND 30
WHERE created_at > '2026-01-01'
-- Prefix (LIKE with leading constant)
WHERE name LIKE 'Ali%' -- uses index
WHERE name LIKE '%ice' -- does NOT use index (leading wildcard)
-- Sorting (if index matches ORDER BY)
ORDER BY name ASC
ORDER BY age DESC
Unique Index
Enforces uniqueness while also providing fast lookups:
-- Create unique index
CREATE UNIQUE INDEX idx_users_email ON users (email);
-- Or as constraint
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
-- Inline during CREATE TABLE
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(200) UNIQUE,
name VARCHAR(100)
);
Attempting to insert a duplicate value raises an error:
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice');
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice2');
-- ERROR 1062: Duplicate entry '[email protected]' for key 'idx_users_email'
Primary Key Index
Every InnoDB table has a clustered primary key index — the table data is physically stored in primary key order. This makes primary key lookups extremely fast.
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10,2),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
InnoDB clustering: The primary key is the clustered index — row data is stored with the index. All other indexes (secondary indexes) store the primary key value as a pointer to the row.
Composite Index
An index on multiple columns. Column order matters significantly:
-- Composite index on (last_name, first_name)
CREATE INDEX idx_name ON users (last_name, first_name);
This index supports:
WHERE last_name = 'Smith' -- uses index
WHERE last_name = 'Smith' AND first_name = 'John' -- uses index (both columns)
WHERE first_name = 'John' -- does NOT use index (leftmost prefix rule)
The Leftmost Prefix Rule
MySQL can use a composite index for any prefix of its columns, starting from the left:
CREATE INDEX idx_composite ON orders (user_id, status, created_at);
-- Uses index (leftmost prefix)
WHERE user_id = 42
WHERE user_id = 42 AND status = 'pending'
WHERE user_id = 42 AND status = 'pending' AND created_at > '2026-01-01'
-- Does NOT use index (skips user_id)
WHERE status = 'pending'
WHERE created_at > '2026-01-01'
Covering Index
A covering index includes all columns needed by a query — MySQL can answer the query from the index alone without touching the table:
-- Query needs: user_id, status, total
CREATE INDEX idx_covering ON orders (user_id, status, total);
-- This query is answered entirely from the index (no table access)
SELECT user_id, status, total FROM orders WHERE user_id = 42;
FULLTEXT Index
Designed for full-text search on text columns. Supports natural language queries and boolean mode:
-- Create full-text index
CREATE FULLTEXT INDEX idx_ft_content ON articles (title, body);
-- Natural language search
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('machine learning');
-- Boolean mode (more control)
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+python -java' IN BOOLEAN MODE);
-- + means must include, - means must exclude
-- With relevance score
SELECT title, MATCH(title, body) AGAINST('python') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('python')
ORDER BY relevance DESC;
Notes:
- Minimum word length is 3 characters by default (
ft_min_word_len) - Common words (stopwords) are ignored
- InnoDB supports FULLTEXT from MySQL 5.6+
SPATIAL Index
For geographic data types (GEOMETRY, POINT, POLYGON, etc.):
CREATE TABLE locations (
id INT PRIMARY KEY,
name VARCHAR(100),
position POINT NOT NULL,
SPATIAL INDEX idx_position (position)
);
-- Insert a point (longitude, latitude)
INSERT INTO locations (name, position)
VALUES ('Eiffel Tower', ST_GeomFromText('POINT(2.2945 48.8584)'));
-- Find locations within a bounding box
SELECT name FROM locations
WHERE MBRContains(
ST_GeomFromText('POLYGON((2.0 48.7, 2.5 48.7, 2.5 49.0, 2.0 49.0, 2.0 48.7))'),
position
);
HASH Index
Used by the MEMORY storage engine. Provides O(1) equality lookups but doesn’t support range queries:
-- MEMORY table with hash index (default for MEMORY)
CREATE TABLE session_cache (
session_id VARCHAR(64) PRIMARY KEY,
data TEXT,
expires DATETIME
) ENGINE=MEMORY;
-- Explicit hash index
CREATE TABLE cache (
key_name VARCHAR(100),
value TEXT,
INDEX USING HASH (key_name)
) ENGINE=MEMORY;
Limitations: No range queries, no ORDER BY optimization, no prefix searches.
Analyzing Index Usage
EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
Key columns to check:
type:constorref= good,ALL= full table scan (bad)key: which index was used (NULL = no index)rows: estimated rows examined (lower is better)Extra:Using index= covering index (excellent)
-- Extended explain
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
Show Index Information
-- Show all indexes on a table
SHOW INDEX FROM users;
-- Show index statistics
SELECT * FROM information_schema.STATISTICS
WHERE table_schema = 'mydb' AND table_name = 'users';
Find Missing Indexes
-- Queries doing full table scans (from slow query log)
-- Enable slow query log:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- log queries > 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';
Index Best Practices
Do Index
-- Columns in WHERE clauses
CREATE INDEX idx_status ON orders (status);
-- Columns in JOIN conditions
CREATE INDEX idx_user_id ON orders (user_id);
-- Columns in ORDER BY (if frequently sorted)
CREATE INDEX idx_created ON orders (created_at);
-- Foreign key columns
CREATE INDEX idx_fk_user ON orders (user_id);
Don’t Over-Index
-- Bad: indexing every column
-- Each index slows down INSERT/UPDATE/DELETE
-- and uses disk space
-- Good: index only what's needed for your queries
-- Analyze slow queries first, then add targeted indexes
Index Maintenance
-- Rebuild fragmented indexes
OPTIMIZE TABLE users;
-- Analyze table statistics (helps query optimizer)
ANALYZE TABLE users;
-- Remove unused indexes (check with performance_schema)
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb' AND count_star = 0;
Quick Reference
| Index Type | Supports | Best For |
|---|---|---|
| B-Tree (default) | Equality, range, prefix, sort | Most queries |
| Unique | Equality + uniqueness | Email, username, SKU |
| Primary Key | Equality (clustered) | Row identification |
| Composite | Multi-column queries | Covering indexes |
| FULLTEXT | Natural language search | Articles, descriptions |
| SPATIAL | Geographic queries | Location data |
| HASH | Equality only | MEMORY tables, caches |
Resources
- MySQL Docs: How MySQL Uses Indexes
- MySQL Docs: EXPLAIN Output
- Use The Index, Luke — free book on SQL indexing
Comments