Why MySQL Indexes Aren’t Always Faster: Models, Types, and Common Pitfalls

In large databases, scanning millions of rows is slow; an index works like a dictionary’s table of contents, allowing MySQL to locate rows quickly while consuming extra storage.

Common index models correspond to three simple data structures:

Hash table : stores key‑value pairs; the key is obtained by hashing the indexed column. Collisions are handled with a linked list, but hash tables are unordered and cannot support range queries.

Ordered array : stores values in sorted order, enabling fast equality and range queries. It is suitable for static data because inserts require shifting subsequent rows.

Search tree (B+ tree) : a balanced tree stored on disk; MySQL InnoDB uses a B+ tree because it reduces disk I/O compared to binary trees.

InnoDB Index Classification

MySQL supports multiple storage engines; the most commonly used is InnoDB, which defines two index types.

Clustered index (primary key) : the table’s rows are stored in primary‑key order, forming a B+ tree whose leaf nodes contain the full row data. Queries that can use the clustered index retrieve rows directly from leaf nodes, making range queries very fast.

Secondary (auxiliary) index : stores only the indexed column’s value and a pointer to the primary key. The engine must perform a “row lookup” (back‑table) to fetch the full row. Secondary indexes can be unique, non‑unique, or composite.

B+ Tree Index Details

InnoDB stores data and indexes in 16 KB pages; each B+ tree node corresponds to a page. Typical tree height is 2–3 levels, so locating a row requires only 2–3 I/O operations, often completing in 0.02–0.03 seconds. If the page is already cached, latency is even lower.

Is an Index Always Faster?

Adding an index does not guarantee speed improvements. Factors such as low selectivity, dirty‑page flushing, mismatched column types, and functions applied to indexed columns can cause queries to remain slow or become slower.

Dirty‑Page Flushing

When rows are inserted or updated, changes are first written to the redo log in memory and later flushed to disk. If the redo log fills up, the system pauses other operations to flush dirty pages, which can temporarily degrade performance.

Field Type Mismatch

If a column’s type does not match the query’s literal type, the optimizer may ignore the index. Example:

CREATE TABLE `test` (
  `id` bigint(10) NOT NULL,
  `a` varchar(128) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Running EXPLAIN SELECT * FROM test WHERE a = '99'; shows the index on a is not used because the column’s selectivity is low.

Functions and Expressions on Indexed Columns

Applying functions or arithmetic to an indexed column prevents index usage. For example:

EXPLAIN SELECT * FROM test WHERE b > 100+1;

EXPLAIN SELECT * FROM test WHERE POW(b,2) > 100;

Both queries will result in full table scans.

Optimizer Choosing the Wrong Index

The MySQL optimizer compares the cost of using an index versus a full table scan. If an auxiliary index’s selectivity is low, the optimizer may prefer a full scan. Selecting appropriate indexes requires evaluating column cardinality and query patterns.

Conclusion

This article covered MySQL index fundamentals, including index models, InnoDB clustered and secondary indexes, B+ tree mechanics, and common pitfalls such as low selectivity, dirty‑page flushing, type mismatches, and function usage that can negate the benefits of indexing.