Understanding Database Indexes: Types, Usage, and Performance

Welcome everyone to click “like” and scan the QR code to follow, encouraging more automation‑testing peers to join the learning community.

Indexes are structures that sort the values of one or more columns in a database table.

Indexes improve retrieval speed and accelerate joins between tables.

Common index types include:

FULLTEXT – full‑text index (MyISAM, InnoDB 5.6+), usable on CHAR, VARCHAR, TEXT columns.

HASH – hash index (supported by MEMORY engine).

BTREE – B‑tree index.

B+TREE – B+‑tree index.

RTREE – R‑tree index.

Index categories and their features:

Normal index – speeds up queries.

Unique index – speeds up queries, enforces column uniqueness, allows NULL.

Primary key index – speeds up queries, column values are unique, cannot be NULL, only one per table.

Composite index – combines multiple columns for multi‑column searches, more efficient than index merging.

Full‑text index – tokenizes text content for search.

Columns that should have indexes:

Fields frequently used in WHERE clauses.

Grouping or ordering fields.

Join columns between tables.

Fixed‑length fields (e.g., CHAR) are better than variable‑length (e.g., TEXT); numeric fields are preferable to strings.

For long text, consider prefix indexes when the leading part is sufficiently selective.

Columns that are unsuitable for indexing:

Fields that are updated frequently.

Fields that never appear in WHERE clauses.

MySQL typically uses B+‑tree structures for indexes.

Additional index types you may encounter include hash indexes and B‑tree indexes.

The time complexities are O(1) for hash indexes and O(log N) for B+‑tree indexes.

Why MySQL still uses B+‑tree indexes despite hash being faster? Hash indexes excel for single‑value lookups (O(1)), but for range queries or when many rows are accessed, B+‑tree’s ordered structure (O(log N)) and linked leaf nodes provide better overall performance. Moreover, B+‑trees store indexes on disk in page‑aligned nodes, allowing efficient I/O by loading only needed pages, which is crucial because disk I/O is far slower than memory access.