30 Essential MySQL Index Optimization Techniques You Must Know

Indexes are ordered copies of data (primarily B+Tree) that improve query performance. The guide is organized into four sections: basic principles, usage tips, advanced maintenance, and scenario‑specific optimizations.

1. Basic Index Creation Principles

Purpose‑driven indexing : create indexes only on columns used in WHERE, ORDER BY, or GROUP BY clauses. CREATE INDEX idx_user_email ON users(email); Left‑most prefix rule : a composite index (A, B, C) can serve queries on (A), (A, B), or (A, B, C). Example:

CREATE INDEX idx_abc ON orders(user_id, status, create_time);

– usable for WHERE user_id=1 AND status=1 but not for WHERE status=1 AND create_time>'2023-01-01'.

High‑selectivity columns first : index columns that are almost unique before low‑cardinality ones. Example of good index: CREATE INDEX idx_phone ON users(phone); vs. bad index on gender.

Avoid over‑indexing : too many indexes slow writes. Remove redundant indexes:

DROP INDEX idx_email_duplicate ON users;

2. Index Usage Tips & Pitfalls (6‑15)

Avoid functions on indexed columns – they invalidate the index. Rewrite SELECT * FROM orders WHERE YEAR(create_time)=2023; as a range query on create_time.

Prevent implicit type conversion – compare strings to strings: WHERE user_id='10086' instead of numeric literal.

Replace <> and NOT IN with IN lists for better index usage.

LIKE patterns : use a prefix pattern ( 'abc%') to use the index; avoid leading wildcards ( '%abc%').

IN / OR : generally index‑friendly, but large IN lists may cause full scans.

Covering indexes : if an index contains all selected columns, the query can be satisfied without accessing the table data. SELECT a,b FROM t WHERE a=1; Index‑driven ordering : ORDER BY a, b can use an index on (a, b); ordering by a non‑leading column cannot.

IS NULL / IS NOT NULL can use an index when the column is indexed. SELECT * FROM users WHERE email IS NULL; Prefix indexes : index only the first N characters, e.g., CREATE INDEX idx_title ON articles(title(20)); Slow query analysis : enable slow query log and use EXPLAIN to inspect execution plans.

3. Advanced & Maintenance Techniques (16‑20)

Reduce index fragmentation : OPTIMIZE TABLE orders; Composite vs. single‑column indexes : a well‑designed composite index often outperforms separate single‑column indexes.

Index push‑down (ICP) in MySQL 5.6+ allows storage engine to filter rows early.

Unique vs. regular indexes : unique indexes enforce uniqueness and may be slightly slower on writes; regular indexes allow duplicates.

FORCE INDEX should be used cautiously, only when the optimizer chooses a sub‑optimal plan.

4. Scenario‑Specific Optimizations (21‑30)

Selectivity vs. condition order : place the most selective column first in the index.

Pagination : avoid large offsets; use sub‑query or keyset pagination for better performance.

Index merge : MySQL can combine multiple single‑column indexes for OR conditions.

GROUP BY implicit ordering : an index on the grouping columns speeds aggregation.

Statistics collection : ANALYZE TABLE orders; keeps optimizer statistics up to date.

Virtual columns + index : generate a computed column (e.g., year from a timestamp) and index it for faster queries.

Sharding key indexing : ensure the sharding column is indexed.

JSON indexes (MySQL 8.0+) : extract a value into a generated column and index it.

Monitor index usage : query performance_schema.table_io_waits_summary_by_index_usage to find unused indexes.

Indexes and locking : add high‑selectivity conditions to UPDATE statements to reduce row locking.

5. Summary & Optimization Loop

Creation principles : left‑most prefix, high selectivity, composite preferred.

SQL usage : avoid functions, type conversion, leading wildcards, and <>.

Performance gains : covering indexes, index‑driven sorting, ICP, virtual columns.

Maintenance : defragment, clean redundant indexes, analyze slow queries.

Optimization loop: slow SQL → EXPLAIN → adjust index/SQL → verify → continuous monitoring.