MySQL Index Design Specification and Best Practices

After introducing basic concepts of index design, this article provides a detailed specification for MySQL index creation.

Index naming conventions: Single‑column indexes should start with idx_ (lowercase). Composite indexes use idx_multi_. Unique indexes use udx_ (e.g., udx_multi_1). Full‑text indexes use ft_ with the ngram parser. Prefix indexes end with _prefix, and function indexes start with idx_func_.

alter table t1 add key idx_r1(r1);
alter table t1 add key idx_multi_1(r1,r2,r3);
alter table t1 add unique key udx_f1(r1);
alter table t1 add key udx_multi_1(r1,r2,r3);
alter table t1 add fulltext ft_r1(r1) with parser ngram;
alter table t1 add key idx_r1_prefix(r1(10));
alter table t1 add key idx_func_r1((mod(r1,4)));

Column selection guidelines: Prefer integer columns for indexes; if strings are necessary, consider hashing them first. Primary keys should also be integer. Control the character length of indexed columns, using prefix indexes when only a portion of a varchar is selective.

alter table t1 add key idx_r1_prefix(r1(10));

Choose columns with high selectivity for indexing, and order composite indexes by decreasing selectivity (e.g., (r1,r2,r3) when r1 has the most distinct values).

Index count control: MySQL allows up to 64 indexes per table, but fewer indexes are better. Avoid indexing columns that are frequently updated (e.g., inventory quantity) and avoid unnecessary function indexes unless the function is required for queries.

Duplicate and overlapping indexes: Avoid multiple indexes that start with the same leftmost column. Consolidate them into a single composite index when possible (e.g., idx_multi_1(r1,r2,r3)). If different query patterns require additional columns, create another composite index (e.g., idx_multi_2(r1,r4,r5)) or merge them into a larger one.

alter table t1 add key idx_multi_1(r1,r2,r3);
alter table t1 add key idx_multi_2(r1,r4,r5);
alter table t1 drop key idx_multi_1, drop key idx_multi_2, add key idx_multi_1(r1,r2,r3,r4,r5);

Small‑table considerations: For tables with very few rows (e.g., 1,000), additional indexes beyond the primary key are often unnecessary because a full table scan can be faster.

select * from t1 where r1 = 10;

Index ordering: By default, indexes are created in ascending order. If queries require specific sort directions, define the index with explicit ASC/DESC for each column.

alter table t1 add key idx_multi_sort_1(r1 desc, r2 asc, r3 desc);

The article concludes that these guidelines form a complete reference for MySQL index design, and readers are encouraged to ask further questions if needed.