Why Indexes Matter in MySQL: Benefits, Types, and Optimization Tips

Indexes guarantee row uniqueness, accelerate data retrieval, and speed up table joins, but they add creation/maintenance overhead and consume storage.

MySQL Storage Engines

MyISAM : high insert and query speed, no transaction support.

InnoDB : transaction‑safe, automatic crash recovery, default engine for most workloads.

The InnoDB buffer pool caches table and index pages to reduce disk I/O. Default size is 128 MiB, page size 16 KiB, and pages are managed with an LRU algorithm.

Index Types

B+‑tree index : the default structure for most MySQL queries.

Full‑text index : supported by MyISAM and, since MySQL 5.6.4, by InnoDB; uses inverted lists for keyword search.

Hash index : InnoDB builds an adaptive hash index on frequently accessed B+‑tree values for faster lookups.

B+‑tree Mechanics

A B+‑tree is a balanced search tree optimized for disk storage. All leaf nodes contain the indexed keys in sorted order and are linked together for sequential scans. The tree height h = log_{m+1}(N), where N is the number of rows and m is the number of entries per disk block; larger m yields a shallower tree.

Typical mechanical‑disk I/O consists of seek time (~5 ms), rotational latency (~4.2 ms for a 7200 RPM drive), and transfer time (<0.1 ms). A single I/O therefore costs roughly 9 ms, during which a modern CPU can execute tens of millions of instructions, making full‑table scans very expensive.

With a three‑level B+‑tree, a row lookup may require only three I/O operations, dramatically reducing latency compared with a scan that would need one I/O per row.

Index Categories in InnoDB

Clustered index (primary key): the table’s data rows are stored in the leaf nodes of this index; a table can have only one.

Secondary index : leaf nodes store the indexed key values plus a pointer to the corresponding clustered index entry.

Composite (multi‑column) index : must be used from the leftmost column; column order matters for range queries and LIKE predicates.

Covering index : contains all columns required by a query, eliminating the need to read the clustered index rows.

Index Condition Pushdown (ICP)

Since MySQL 5.6, InnoDB can evaluate filter conditions while scanning a secondary index, reducing the number of rows that need to be fetched from the clustered index. The optimizer shows Using index condition in the Extra column of EXPLAIN.

Practical Index Optimization Techniques

Force a specific index with FORCE INDEX(index_name).

Enable Multi‑Range Read (MRR) for range, ref, or eq_ref queries to convert random I/O into sequential I/O.

Prefer smaller data types (e.g., INT instead of VARCHAR) to reduce storage, memory footprint, and CPU‑cache pressure.

Avoid NULL columns when possible; use NOT NULL or sentinel values to keep indexes efficient.

Key Takeaways

InnoDB locks only the rows it accesses; indexes reduce the number of rows examined.

Follow the left‑most prefix rule: MySQL can use an index only up to the first range condition.

Equality and IN predicates can be reordered; the optimizer will match them to the index order.

Choose columns with high selectivity for indexing.

Do not apply functions to indexed columns in WHERE clauses; rewrite expressions to use raw column values.

Extend existing indexes instead of creating redundant ones when possible.