MySQL Index Design Principles and Common Index Pitfalls

Previously we introduced the principles of indexes and query rules; now we review the basic principles for creating MySQL indexes and list all the design rules that should be followed.

MySQL maintains a B+‑tree for the primary key, known as a clustered index. Non‑primary (usually composite) indexes store the indexed column values in order and compare subsequent columns when earlier values are equal.

If all values in a composite index are identical, MySQL falls back to the primary key for ordering. A clustered index stores the full row, while a non‑clustered index stores only the indexed columns and the primary key.

Primary key index : Use an auto‑increment integer as the primary key; avoid using UUID because it is unordered and forces costly page inserts.

For frequently queried columns, create indexes to speed up lookups; usually this means composite indexes because queries often involve multiple conditions.

Avoid indexing large fields; prefer small‑size columns (e.g., varchar(5) instead of varchar(200) ) because larger fields increase index size and comparison cost.

If a column is large, you can index a prefix, e.g., CREATE INDEX tbl_address ON dual(address(20)); .

Choose columns with high cardinality (high distinct value count). Low‑cardinality columns (e.g., gender) are poor index candidates because the optimizer may ignore them and perform a full table scan.

Index columns used in ORDER BY or GROUP BY to avoid extra sorting; otherwise MySQL may need to create a temporary sorted table.

Avoid using functions on indexed columns because the function changes the expression and prevents index usage. If you must query with a function, create a functional index, e.g., CREATE INDEX stu_age_round ON test(ROUND(age)); .

SELECT * FROM student WHERE ROUND(age) = 2;

Do not create too many indexes; each index adds storage overhead and maintenance cost, especially for columns that are frequently updated.

Do not index columns that change often, as each update forces a rebuild of the index.

Common index‑ineffective scenarios :

Using OR without indexing every column involved.

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

Leading wildcard in LIKE patterns (e.g., LIKE '%text' ).

Implicit type conversion, such as comparing an integer column to a string literal ( WHERE age='15' ).

Example of type conversion causing index loss:

SELECT * FROM student WHERE age = 15;   -- uses index

SELECT * FROM student WHERE age = '15'; -- does NOT use index

When the column’s cardinality is low, the optimizer may also ignore the index, treating the query as a full scan.

Overall, index design must be guided by actual business needs; there is no universal formula, and over‑optimizing can be counter‑productive.