MySQL Index Optimization: Types, Pitfalls, and EXPLAIN Guide for Beginners

What Is an Index?

An index is like a dictionary's radical index — it doesn't store actual data, only location pointers. MySQL's most common index is the B+Tree index, which works like a building's floor directory: fewer levels mean faster lookups.

Example: A user table with 100,000 rows. Without an index, querying id=10086 requires scanning all 100,000 rows. With an index on id, MySQL jumps directly to the physical location instantly.

MySQL Index Types

By Function

Primary Key Index

Unique Index

Normal Index

Composite Index (multi-column)

Fulltext Index (for long-text search, often replaced by Elasticsearch)

Spatial Index (for geospatial data)

By Storage Structure

B+Tree Index (99%+ of scenarios)

Hash Index (Memory engine only, equality only, no range/sort)

R-Tree Index (for spatial indexes)

Four Core Indexes for 80%+ of Work

2.1 Primary Key Index (PRIMARY KEY) — Mandatory

Unique, not null, one per table. Identifies each row (e.g., user.id, order.order_id).

Best practice: Use auto-increment integers (INT, BIGINT). Avoid strings like UUID — they waste space, sort slower, and random inserts fragment the index.

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 Unique Index (UNIQUE) — For Uniqueness Constraints

Ensures column values are unique (e.g., phone, email, ID card). Allows NULL. Multiple per table. Inserting duplicates throws an error.

-- At table creation
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add to existing table
ALTER TABLE `user` ADD UNIQUE KEY `idx_phone` (`phone`);

2.3 Normal Index (INDEX) — For High-Frequency Query Columns

No uniqueness constraint. Allows duplicates and NULL. Multiple per table. Create on columns frequently used in WHERE, ORDER BY, or JOIN.

-- Add to existing table
ALTER TABLE `user` ADD INDEX `idx_name` (`name`);

-- At table creation
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_phone` (`phone`),
  INDEX `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.4 Composite Index — For Multi-Condition Queries (Key!)

Multiple columns combined into one index (e.g., (name, phone)). Optimizes both single-column and multi-column filters. More efficient than separate single-column indexes, saves disk space.

Critical rule: Leftmost Prefix Principle — queries must start with the first column of the composite index to use it. Skipping the first column makes the index ineffective.

-- Add composite index
ALTER TABLE `user` ADD INDEX `idx_name_phone` (`name`, `phone`);

-- At table creation
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_phone` (`phone`),
  INDEX `idx_name` (`name`),
  INDEX `idx_name_phone` (`name`, `phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Naming convention: unique indexes as uniq_column, normal/composite as idx_col1_col2. Primary key is auto-named PRIMARY.

Six Index Optimization Pitfalls (90% Fall Into)

Pitfall 1: More Indexes = Better — Wrong!

Indexes consume disk space. Every INSERT/UPDATE/DELETE must update all indexes — like rewriting every directory when the dictionary changes. More indexes = slower writes.

Fix: Index only high-frequency query columns. Keep indexes per table under 5.

Pitfall 2: Indexing Low-Frequency Columns

Example: address rarely queried but indexed — wastes space, slows writes.

Fix: Analyze business queries; index only columns used in WHERE, ORDER BY, JOIN.

Pitfall 3: Using Strings as Primary Keys

UUIDs or phone numbers as PKs: strings use more space, compare character-by-character (slower than integers), and random UUIDs fragment the B+Tree on insert.

Fix: Prefer auto-increment INT/BIGINT. If strings are mandatory, keep them short and unique.

Pitfall 4: Query Patterns That Break Index Usage

Even with indexes, bad SQL causes full table scans. Common mistakes:

-- Mistake 1: Function on indexed column
SELECT * FROM `user` WHERE SUBSTR(phone, 1, 3) = '138'; -- index lost
-- Fix: avoid functions on indexed columns
SELECT * FROM `user` WHERE phone LIKE '138%';

-- Mistake 2: !=, NOT IN, IS NOT NULL on indexed column
SELECT * FROM `user` WHERE phone != '13800138000'; -- full scan
-- Fix: use =, IN, IS NULL; rewrite if needed

-- Mistake 3: Arithmetic on indexed column
SELECT * FROM `user` WHERE id + 1 = 10087; -- index lost
-- Fix: move arithmetic to right side
SELECT * FROM `user` WHERE id = 10087 - 1;

-- Mistake 4: Leading wildcard in LIKE
SELECT * FROM `user` WHERE name LIKE '%老唐'; -- index lost
-- Fix: wildcard at end only
SELECT * FROM `user` WHERE name LIKE '老唐%';

Rule: Keep indexed columns "naked" — no functions, arithmetic, or leading wildcards.

Pitfall 5: Redundant Indexes

Examples: PK on id plus a normal index on id; composite (name, phone) plus separate name index. Redundant indexes waste space and slow writes.

Fix: Audit indexes regularly. Drop redundant ones — PK covers id lookups; composite covers prefix columns.

Pitfall 6: Ignoring Composite Indexes for Multi-Condition Queries

Query: WHERE name='老唐' AND phone='13800138000'. Separate indexes on name and phone are far less efficient than a single (name, phone) composite index.

Remember leftmost prefix: name=... AND phone=... uses the composite index; phone=... alone does not.

Two Tools to Verify Index Effectiveness

4.1 EXPLAIN — View Execution Plan (Most Used)

Prefix query with EXPLAIN. Focus on two columns:

type : ref or range = index used; ALL = full table scan.

key : shows index name (e.g., idx_name) if used; NULL if not.

EXPLAIN SELECT * FROM `user` WHERE name = '老唐';

Example: type=ref, key=idx_name → index works. type=ALL, key=NULL → index ignored; check SQL or index definition.

4.2 SHOW INDEX — List All Table Indexes

Shows every index: name, type, columns, etc. Useful for spotting redundant or missing indexes.

SHOW INDEX FROM `user`;

Summary Mnemonic

Primary key: auto-increment integer. Unique index: enforce uniqueness. High-frequency queries: normal index. Composite index: leftmost prefix match. No functions/arithmetic on indexed columns. Drop redundant indexes. EXPLAIN to verify. Build indexes on demand — don't overdo it.

Start with "no wrong indexes, no redundancy, indexes actually used." Optimize iteratively as business understanding grows.