Mastering MySQL Indexes: Types, Pros, Cons, and Implementation Details

What is an Index

An index is a data structure that stores the values of one or more columns in sorted order. By keeping the values sorted, the database can locate rows by binary search or tree traversal instead of scanning the entire table, similar to a book's table of contents.

Advantages of Indexes

Unique indexes enforce row‑level uniqueness.

Searches examine far fewer rows, dramatically reducing query latency.

Join operations become faster because matching rows are found via indexed columns.

GROUP BY and ORDER BY clauses can be satisfied using the index order, lowering sorting cost.

The optimizer can choose more efficient execution plans when suitable indexes exist.

Disadvantages of Indexes

Creating and maintaining indexes consumes CPU and I/O; the cost grows with data volume.

Indexes occupy additional storage. Clustered (primary) indexes store the full row in leaf pages, which can be sizable.

INSERT, UPDATE, and DELETE operations must modify every affected index, slowing write performance.

Guidelines for Creating Indexes

Columns that appear frequently in WHERE predicates.

Primary‑key columns, which guarantee uniqueness and define the physical row order.

Foreign‑key columns used in joins.

Columns involved in range queries (e.g., BETWEEN, >=, <) because ordered indexes support interval scans.

Columns used for ORDER BY so the query can reuse the index order.

Columns that are part of composite indexes when the leading column(s) are selective.

When Not to Create Indexes

Columns rarely referenced in queries; the maintenance overhead outweighs any benefit.

Very low‑cardinality columns (e.g., gender) where most rows share the same value.

Large or binary columns such as TEXT, IMAGE, or BIT that have few distinct values.

Workloads where write throughput is far more critical than read performance.

Index Data Structures

MySQL supports several index implementations, the most common being:

B+Tree

Hash

FullText

R‑Tree

Hash Index

Overview

Only the Memory storage engine provides hash indexes. A hash index stores the hash of the indexed columns as the key, enabling constant‑time lookups for equality predicates.

Drawbacks

Supports only equality conditions; range queries are impossible. Cannot be used for ordering or sorting. Hash collisions require additional row comparisons, degrading performance. Performance drops when many distinct keys map to the same hash bucket. Partial‑key queries are ineffective because the hash is computed on the full column set.

FullText Index

Overview

Full‑text indexes are available only with the MyISAM engine for CHAR , VARCHAR , and TEXT columns. They replace slow LIKE pattern matches and enable multi‑field fuzzy searches.

Storage Structure

Internally a B‑Tree is used, but the leaf nodes store tokenized word fragments together with a pointer to the original text. This allows the engine to retrieve matching rows after tokenization.

B+Tree Index

B+Tree is the default and most widely used index type in MySQL (both InnoDB and MyISAM). Compared with hash indexes, B+Tree supports range scans and ordered retrieval.

Leaf pages contain a sequential pointer to the next leaf, enabling efficient interval scans (e.g., keys 18‑49 can be read by following the leaf chain).

Each node is sized to match a disk page (commonly 4 KB), so a single I/O loads an entire node.

Search depth is h‑1 I/Os because the root stays in memory; the asymptotic cost is O(log_d N), where the branching factor d is often > 100, making h usually ≤ 3.

Internal nodes store only keys, while leaf nodes store the full row (InnoDB) or the row address (MyISAM), reducing internal node size and I/O.

MySQL Index Implementations

MyISAM

Primary (Unique) Index

MyISAM uses a B+Tree where each leaf data field holds the physical address of the row. The diagram below illustrates a primary index on column Col1 .

Secondary (Non‑Unique) Index

Secondary indexes have the same B+Tree shape, but the leaf data field stores the row address rather than the key itself. This is a non‑clustered index.

InnoDB

Primary (Clustered) Index

InnoDB stores the table data itself as a B+Tree. Leaf nodes contain the complete row, making the primary key a clustered index. If a table lacks an explicit primary key, InnoDB creates a hidden 6‑byte integer key.

Secondary Index

InnoDB secondary indexes store the primary‑key value in the leaf data field rather than a row address. A lookup therefore requires two steps: (1) search the secondary index to obtain the primary key, and (2) fetch the row from the clustered primary index.

Because secondary indexes reference the primary key, a wide primary key inflates every secondary index. Keeping the primary key narrow minimizes overall index size.