Mastering MySQL Indexes: Boost Query Performance with B‑Tree, Prefix, and Composite Strategies

Introduction

Before building high‑performance indexes, it is essential to understand the fundamentals of MySQL indexing, B+ tree storage, and the role of primary and secondary indexes.

B+ Tree Storage Structure

MySQL uses a B+ tree to store index information. The leaf nodes contain the indexed column values and a pointer to the full row (the primary key), while internal nodes store only the key values and child pointers. Each page is typically 16 KB, and data is read page‑wise into memory.

Types of Indexes

Clustered Index (Primary Key) : The table data is stored in the leaf nodes of the B+ tree.

Secondary (Non‑Clustered) Index : Leaf nodes store the indexed column value and the primary key of the row.

Index Selectivity

Selectivity is the ratio of distinct values to total rows. High selectivity (close to 1) means the index can quickly narrow down the search range, while low selectivity approaches a full table scan.

Prefix Indexes

For long character columns, a prefix index stores only the first n characters, reducing index size and improving I/O. The optimal prefix length balances selectivity and storage cost.

Composite Indexes and the Leftmost Principle

Composite indexes are ordered by the columns as defined. MySQL can use the index only for the leftmost contiguous columns until it encounters a range condition ( >, <, BETWEEN, LIKE '%...'). Example: an index on (empname, depno) can satisfy WHERE empname='X' AND depno=10 but not WHERE depno=10 AND empname='X' if empname is not the first column.

Covering Indexes

If a query can be satisfied entirely from the index (all selected columns are in the index), MySQL avoids a “back‑lookup” to the primary key, dramatically reducing I/O.

Index Condition Pushdown (ICP)

ICP allows the storage engine to apply additional filter conditions using the index, reducing the number of rows sent to the server. For example, a combined index on (empname, sal) can satisfy WHERE empname='John' AND sal=2000 without a back‑lookup.

When Indexes Become Ineffective

Applying functions to indexed columns (e.g., ABS(id) = 5) forces a full scan.

Type conversion mismatches, such as comparing a string column to a numeric literal, cause MySQL to cast the column and ignore the index.

Using a leading wildcard in LIKE '%value%' prevents index usage; LIKE 'value%' can still use the index.

Expressions or arithmetic on indexed columns (e.g., id+1 = 100) also disable index usage.

Indexing for Sorting

Including the ORDER BY column in a composite index (e.g., (depno, hiredate)) allows MySQL to retrieve rows already sorted, eliminating an extra sorting step.

Practical Recommendations

Prefer indexes with high selectivity; avoid indexing low‑cardinality columns.

Choose the shortest effective prefix length for long text columns.

Design composite indexes following the leftmost principle and place the most selective columns first.

Use covering indexes to eliminate back‑lookups.

Leverage ICP by creating indexes that include all filter conditions.

Avoid functions, type casts, and leading wildcards on indexed columns.

Include ORDER BY columns in indexes when sorting large result sets.

Conclusion

Understanding index selectivity, prefix length, composite index ordering, and advanced features like covering indexes and ICP enables developers to write queries that fully exploit MySQL’s indexing capabilities, resulting in faster data retrieval and more efficient database operations.