Master MySQL Index Design: Essential Principles for Optimal Performance

Review of Index Principles

Previously we introduced the theory of indexes and query principles; now we focus on the practical rules for designing MySQL indexes so that they follow the "standard" and work efficiently.

Clustered (Primary) and Secondary Indexes

MySQL maintains a B+‑tree for the primary key (clustered index). The tree stores the full row data. Non‑primary (secondary) indexes store only the indexed column values and the primary key.

If all values in a composite index are identical, MySQL falls back to the primary key for ordering. Secondary indexes do not contain the full row.

Primary Key Design

Use an auto‑increment integer as the primary key; avoid UUID because it is unordered and forces MySQL to insert records by scanning the B+‑tree, which hurts performance.

When the primary key is auto‑increment, MySQL can quickly locate the insertion point. With a non‑sequential key, each insert requires a full scan of the tree.

Unique Indexes

Unique indexes behave like primary keys but are not necessarily auto‑increment. Maintaining them costs more than a primary key, though they allow fast look‑ups (sometimes requiring a table lookup if the indexed column is not the primary key).

Guidelines for Index Creation

Index frequently queried columns : fields that appear often in WHERE clauses should have indexes.

Use composite indexes when queries involve multiple columns.

For LIKE patterns, follow the left‑most prefix rule.

Avoid indexing large fields; prefer short columns (e.g., varchar(5) over varchar(200)).

If a column is large, index a prefix (e.g., address(20)).

Choose columns with high cardinality; low‑cardinality columns (e.g., gender) rarely benefit from indexing.

Index columns used in ORDER BY and GROUP BY to eliminate extra sorting.

Do not create excessive indexes; each index adds storage and maintenance overhead.

Avoid indexing columns that change frequently, as updates require costly index rebuilds.

Common Index Failure Scenarios

Using OR without indexing every column involved.

Composite indexes that do not follow the left‑most prefix rule.

Wildcard LIKE patterns that start with %.

Implicit type conversion (e.g., comparing an integer column to a quoted string).

Mismatch between GROUP BY and ORDER BY columns, even if both are indexed.

Using functions on indexed columns (e.g., ROUND(age)) disables the index unless the function is part of the index definition.

Applying functions or expressions that are not stored in the index.

Code Examples

CREATE INDEX tbl_address ON dual(address(20));

Creating a prefix index for a large VARCHAR column. SELECT * FROM student WHERE ROUND(age) = 2; When a function is used, the normal index on age is ignored. CREATE INDEX stu_age_round ON test(ROUND(age)); Now queries using ROUND(age) can use the index.

SELECT * FROM student WHERE age = 15;

SELECT * FROM student WHERE age = '15';

The second query forces a type conversion and prevents index usage.

Visual Aid

Understanding these principles helps you design efficient indexes and avoid common pitfalls that lead to performance degradation.