Understanding MySQL Indexes: Types, Structures, and Performance Impact

Interview Scenario

The article starts with a mock interview where the candidate mentions hearing about B‑tree and B+‑tree indexes but not hash indexes.

What Happens Without an Index?

If we run a simple query like SELECT * FROM t_employee WHERE name = 'leixiaoshuai' on a table with tens of thousands of rows, MySQL must scan every row to find matching names, resulting in a full‑table scan.

How Indexes Improve Performance

Indexes act like a book's table of contents, allowing the database to locate rows quickly by reducing the number of rows it needs to examine.

What Is an Index?

An index is a data structure—most commonly a B+‑tree—created on one or more columns of a table.

Index Data Structures

MySQL supports two main index types: hash indexes and B+‑tree indexes (the default for InnoDB). B+‑trees provide ordered storage and logarithmic‑time search, insert, and delete operations. Hash indexes use a hash function to map keys directly to locations, offering constant‑time lookups for equality searches.

How Hash Indexes Work

When a hash index is defined, the column values become keys, and each key points to the corresponding row. This can dramatically speed up equality queries but cannot support range queries or ordered scans.

Drawbacks of Hash Indexes

Do not support range queries.

Cannot be used for index‑only sorting.

Do not follow the left‑most prefix rule for composite indexes.

Other Index Types

Additional index types include R‑tree (useful for spatial queries) and bitmap indexes (suitable for boolean columns).

Why Indexes Speed Up Queries

Because indexes store column values in an ordered structure (e.g., B+‑tree), the database can quickly locate matching rows and also retrieve a pointer to the full row data.

What Is Stored in an Index?

An index contains the indexed column’s values and a pointer to the corresponding row in the table; it does not store values of other columns.

How the Database Chooses an Index

When a query runs, MySQL checks whether an index exists on the referenced columns and then decides if using it is more efficient than a full‑table scan.

Forcing Index Usage

Generally, you let the optimizer decide; explicit hints can be used but are not covered here.

Creating an Index in SQL

CREATE INDEX name_index ON t_employee (name);

Creating a Composite Index

CREATE INDEX age_address_index ON t_employee (age, address);

Analogy

An index is like a book’s index: reading the whole book is a full‑table scan, while consulting the index lets you jump directly to the relevant sections.

Cost of Indexes

Indexes consume storage space and add overhead to INSERT, UPDATE, and DELETE operations. The guiding principle is to index columns that are frequently used in queries.

-- End --