MySQL Indexing Strategies – Complete Guide (2025)

Master Performance Tuning with Smart Indexing

MySQL Indexing Strategies – Complete Guide (2025)

Master Performance Tuning with Smart Indexing

MySQL Indexing Strategies – Complete Guide (2025)

Master Performance Tuning with Smart Indexing

Why Indexing Matters

Without Index	With Index
Full table scan (O(n))	Index lookup + few rows (O(log n))
Slow `WHERE`, `JOIN`, `ORDER BY`	Lightning-fast
High CPU & I/O	Low resource usage

Rule: Every WHERE, JOIN, ORDER BY, GROUP BY column should be indexed — selectively.

1. Types of Indexes in MySQL

Index Type	Storage Engine	Use Case
B-Tree (Default)	InnoDB, MyISAM	Most cases (`=`, `<`, `>`, `BETWEEN`, `IN`, `LIKE 'prefix%'`)
Hash	Memory, NDB	Only equality (`=`) – not for range
Full-Text	InnoDB, MyISAM	Text search (`MATCH...AGAINST`)
Spatial	InnoDB	GIS data (`ST_Contains`, etc.)

2. Core Indexing Strategies

Strategy 1: Index Columns in WHERE Clause

-- Bad (full scan)
SELECT * FROM users WHERE email = 'john@example.com';

-- Good (index)
CREATE INDEX idx_email ON users(email);

High Selectivity = Best Candidates

Selectivity = Unique values / Total rows
Example: email (99%) > gender (50%) > active (2%)

Strategy 2: Composite (Multi-Column) Indexes

-- Query
SELECT * FROM orders 
WHERE customer_id = 5 AND status = 'shipped'
ORDER BY order_date DESC;

Best Index:

CREATE INDEX idx_cust_status_date 
ON orders(customer_id, status, order_date);

Leftmost Prefix Rule

Index: `(A, B, C)`	Usable Queries
`WHERE A=...`	Yes
`WHERE A=... AND B=...`	Yes
`WHERE A=... AND B=... AND C=...`	Yes
`WHERE B=...`	No
`WHERE C=...`	No

Order matters: Put most selective column first.

Strategy 3: Covering Indexes (Index-Only Scans)

Return data directly from index → No table lookup!

-- Query
SELECT customer_id, order_date 
FROM orders 
WHERE customer_id = 100;

-- Covering Index
CREATE INDEX idx_covering 
ON orders(customer_id, order_date);

Check with EXPLAIN:

EXPLAIN SELECT ...;
-- Look for: "Using index" in Extra column → WIN!

Strategy 4: Index for JOINs

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Index both sides:

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_customers_id ON customers(id); -- Usually PK

Foreign Key? → Always index it!

Strategy 5: Index for ORDER BY / GROUP BY

-- Slow without index
SELECT * FROM logs 
WHERE app = 'web' 
ORDER BY created_at DESC 
LIMIT 10;

Fix:

CREATE INDEX idx_app_time ON logs(app, created_at DESC);

Use DESC in index if sorting descending.

Strategy 6: Avoid Over-Indexing

Problem	Solution
Too many indexes	Slow `INSERT`, `UPDATE`, `DELETE`
Duplicate indexes	Remove redundant ones

-- Find duplicates
SHOW INDEX FROM orders;

-- Drop redundant
DROP INDEX idx_old ON orders;

3. Advanced Indexing Strategies

A. Partial (Prefix) Indexes – Save Space

CREATE INDEX idx_email_prefix ON users(email(50));
-- Only index first 50 chars of email

Use when: Long VARCHAR, values unique in first N chars.

B. Expression (Functional) Indexes – MySQL 8.0+

-- Index on lowercase email
CREATE INDEX idx_lower_email 
ON users( (LOWER(email)) );

-- Query must match
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

C. Filtered (Partial) Indexes – Not in MySQL

Use generated columns + index instead:

ALTER TABLE users ADD COLUMN is_active TINYINT 
GENERATED ALWAYS AS (IF(status = 'active', 1, 0)) STORED;

CREATE INDEX idx_active ON users(is_active);

D. Clustered vs Secondary Indexes (InnoDB)

Type	Description
Clustered	Primary Key → Data stored in index order
Secondary	Separate structure → points to PK

InnoDB: Data = clustered index
→ PK choice is critical!

4. Best Practices Cheat Sheet

Rule	Command
Always index `PRIMARY KEY`	`PRIMARY KEY(id)`
Index all `FOREIGN KEY`s	`INDEX(customer_id)`
Use `EXPLAIN` before/after	`EXPLAIN SELECT ...`
Avoid `SELECT *` in indexed queries	Use only needed columns
Keep indexes lean	Avoid indexing entire `TEXT`/`BLOB`
Monitor index usage	`SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;`

5. Index Maintenance

Find Unused Indexes

SELECT * 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE INDEX_NAME IS NOT NULL 
  AND COUNT_FETCH = 0;

Rebuild Index

ALTER TABLE orders ENGINE=InnoDB;  -- Rebuilds all indexes
-- or
OPTIMIZE TABLE orders;

6. Common Anti-Patterns (Avoid!)

Anti-Pattern	Why Bad
`WHERE SUBSTRING(email,1,5) = 'john'`	Can’t use index
`ORDER BY RAND()`	Full scan
`LIKE '%search%'`	No index (unless Full-Text)
Indexing low-selectivity columns	`gender`, `status = 1`
Too many indexes (>5–7 per table)	Hurts writes

7. Full-Text Search Indexing

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);

SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('mysql performance');

Use for: Search engines, blogs, forums

8. Real-World Example: E-Commerce

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    status ENUM('pending','shipped','delivered'),
    total DECIMAL(10,2),
    created_at DATETIME,
    INDEX idx_cust_status (customer_id, status),
    INDEX idx_covering (customer_id, created_at, total),
    INDEX idx_status_date (status, created_at DESC)
);

Queries it speeds up:

-- Dashboard
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- Customer history
SELECT total, created_at FROM orders 
WHERE customer_id = 123 ORDER BY created_at DESC;

9. Quiz: Choose the Best Index

Query	Best Index
`WHERE email = ?`	`INDEX(email)`
`WHERE zipcode = ? AND active = 1`	`INDEX(zipcode, active)`
`WHERE phone LIKE '555%'`	`INDEX(phone)`
`WHERE phone LIKE '%555'`	No index
`ORDER BY created_at DESC LIMIT 10`	`INDEX(created_at DESC)`

10. Tools & Commands

-- See all indexes
SHOW INDEX FROM table_name;

-- Analyze query plan
EXPLAIN FORMAT=JSON SELECT ...;

-- Check index usage (MySQL 8.0+)
SELECT * FROM sys.schema_index_statistics;

-- Find missing indexes (slow queries)
-- Use: pt-index-usage, EverSQL, MySQL Workbench

Final Checklist: Index Like a Pro

[ ] Index WHERE, JOIN, ORDER BY, GROUP BY columns
[ ] Use composite indexes wisely (leftmost prefix)
[ ] Create covering indexes for frequent queries
[ ] Foreign keys = indexed
[ ] Use EXPLAIN to validate
[ ] Remove unused/duplicate indexes
[ ] Monitor with performance_schema

Resources

MySQL Index Docs
Use The Index, Luke! – SQL indexing bible
Tools: Percona Toolkit, MySQL Enterprise Monitor

You now have pro-level MySQL indexing skills!
Apply these strategies → 10x faster queries.

Want a PDF cheat sheet or index audit script? Just ask!

Last updated: Nov 09, 2025

MySQL Indexing Strategies – Complete Guide (2025)

Master Performance Tuning with Smart Indexing