Understanding MySQL Indexes: Models, Maintenance, Utilization, and Optimization

MySQL indexes are essential for accelerating data retrieval, and this article explains their underlying data structures, maintenance processes, and practical usage patterns.

Index Models : Three classic models are introduced – hash tables (fast inserts but unordered lookups), ordered arrays (binary search with costly inserts), and B+ trees (the default InnoDB structure that balances read efficiency and disk I/O).

Index Maintenance : Each InnoDB index is a B+ tree. Primary keys form clustered indexes storing full rows, while secondary indexes store only the primary key. Insertions may cause page splits, and change buffers can defer writes for non‑unique indexes, reducing random disk I/O.

Index Utilization : A sample query select * from T where k between 1 and 3; demonstrates how MySQL traverses the k‑index, fetches primary keys, and then accesses the clustered index (the “back‑table” step). Converting the query to select id from T where k between 1 and 3; creates a covering index that eliminates the back‑table lookup.

Optimization Techniques :

Left‑most prefix principle – only the leftmost columns of a composite index can be used.

Index push‑down (available since MySQL 5.6) – filters rows during index traversal, reducing back‑table accesses.

Unique indexes – guarantee at most one matching row, allowing early termination of scans, but they cannot use change buffers for out‑of‑memory pages.

Choosing the right index involves selecting frequently queried columns, columns used in WHERE clauses, columns involved in sorting, and columns used for joins. Composite indexes (e.g., (city, name) or (city, name, age) ) can serve both filtering and ordering, turning a query into a covering index and removing the need for temporary files or filesort.

Common Index Pitfalls include:

Using SELECT * which may trigger a full table scan.

Violating the left‑most prefix rule.

Applying functions or type casts on indexed columns (e.g., where month(modified)=6 or where age=20 when age is a VARCHAR).

Leading‑wildcard LIKE patterns ( LIKE '%abc' ).

OR conditions where one side lacks an index.

Additional examples illustrate how function calls ( month(modified) ), type conversion ( CAST(age AS SIGNED) ), and character‑set conversion ( CONVERT(T1.age USING utf8mb4) ) all cause index loss.

The article also contrasts B‑trees and B+‑trees, explains the purpose of the EXPLAIN statement for viewing execution plans, and advises limiting the number of indexes per table (typically ≤5) to avoid optimizer overhead.