Understanding MySQL Handler_read_* Status Variables and Their Impact on Query Execution

Author: Gao Peng (高鹏). This learning record uses Percona Server 5.7.14 to explore the meaning and implementation of MySQL status variables that start with Handler_read_ , focusing on the InnoDB storage engine.

1. The essence of Handler_read_* variables

MySQL defines the variables in STATUS_VAR as follows:

{"Handler_read_first",       (char*) offsetof(STATUS_VAR, ha_read_first_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
{"Handler_read_key",         (char*) offsetof(STATUS_VAR, ha_read_key_count),   SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
{"Handler_read_last",        (char*) offsetof(STATUS_VAR, ha_read_last_count),  SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
{"Handler_read_next",        (char*) offsetof(STATUS_VAR, ha_read_next_count),  SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
{"Handler_read_prev",        (char*) offsetof(STATUS_VAR, ha_read_prev_count),  SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
{"Handler_read_rnd",         (char*) offsetof(STATUS_VAR, ha_read_rnd_count),   SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
{"Handler_read_rnd_next",    (char*) offsetof(STATUS_VAR, ha_read_rnd_next_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}

These counters are defined at the MySQL layer because MySQL can host multiple storage engines; each engine must update the counters through its own interface.

2. Detailed explanation of each variable

Handler_read_key

Internal name: ha_read_key_count

InnoDB implementation: ha_innobase::index_read

Documentation: "The number of requests to read a row based on a key. If this value is high, it indicates that tables are properly indexed for the queries."

Source‑code meaning: positions an index cursor to the specified index and fetches the row.

Author’s note: used when the engine needs to locate the starting position of an index lookup.

Handler_read_next

Internal name: ha_read_next_count

InnoDB implementation: ha_innobase::index_next_same (or ha_innobase::index_next )

Documentation: "The number of requests to read the next row in key order. Incremented during range scans or index scans."

Source‑code meaning: index_next reads the next row after a previous index_read ; index_next_same reads the next row that matches the same key value.

Author’s note: wraps ha_innobase::general_fetch ; the two functions differ in the way they continue the scan (range vs. ref).

Handler_read_first

Internal name: ha_read_first_count

InnoDB implementation: ha_innobase::index_first

Documentation: "The number of times the first entry in an index was read. A high value suggests many full‑index scans."

Source‑code meaning: positions a cursor on the first record of an index and reads the row.

Author’s note: essentially the same as calling ha_innobase::index_read for the first row.

Handler_read_rnd_next

Internal name: ha_read_rnd_next_count

InnoDB implementation: ha_innobase::rnd_next

Documentation: "The number of requests to read the next row in the data file (table scan). A high value indicates many full table scans."

Source‑code meaning: reads the next row during a sequential table scan (also used for the first row).

Author’s note: used by ha_innobase::general_fetch after index_first when scanning the whole table.

Handler_read_rnd

Internal name: ha_read_rnd_count

InnoDB implementation: ha_innobase::rnd_pos (Memory engine: ha_heap::rnd_pos )

Documentation: "The number of requests to read a row based on a fixed position. High values indicate many queries that require sorting or full‑table scans."

Author’s note: observed mainly when sorting temporary tables with the Memory engine.

Other variables (Handler_read_last, Handler_read_prev)

Handler_read_last – InnoDB: ha_innobase::index_last . Used for the last index entry, often to avoid sorting in DESC scans.

Handler_read_prev – InnoDB: ha_innobase::index_prev . Used for the previous index entry, also to avoid sorting.

3. Common query tests

Creating two tables (z1 and z10) with an integer column a and a simple index, then running various SELECT statements while flushing status and checking SHOW STATUS LIKE 'Handler_read%' reveals how each counter changes.

Full table scan

mysql> desc select * from z1;

Result shows type = ALL and after execution Handler_read_rnd_next increments by the number of rows scanned.

Full index scan

mysql> desc select a from z1;

Result shows type = index and Handler_read_next increments, while Handler_read_key increments only once for the initial positioning.

Index ref access (force index)

mysql> desc select * from z1 force index(a) where a=10;

Result shows type = ref ; after execution Handler_read_key increments once and Handler_read_next increments by the number of matching rows.

Index range access

mysql> desc select * from z1 force index(a) where a>9 and a<12;

Result shows type = range ; the counters reflect a combination of key positioning and sequential reads within the range.

Join with driven table using index

mysql> desc select * from z1 STRAIGHT_JOIN z10 force index(a_idx) on z1.a=z10.a;

The driven table (z1) performs a full scan ( type = ALL ) while the driving table (z10) uses type = ref . Counters show Handler_read_first for the driven scan and Handler_read_key / Handler_read_next for the index look‑ups.

Sorting avoidance (order by with index)

mysql> select * from z1 force index(a) order by a;

Shows Handler_read_next incremented for the ordered scan; the reverse order ( order by a desc ) increments Handler_read_last and Handler_read_prev , demonstrating how MySQL can avoid an explicit sort.

4. Summary

Handler_read_rnd_next usually indicates a full table scan.

Handler_read_first often represents the start of a full table or full index scan.

Handler_read_next typically means a proper index usage or a full index scan.

Handler_read_key increments whenever an index is positioned, regardless of scan type.

In InnoDB, a full table scan is internally a full primary‑key index scan.

Sequential row access is implemented by ha_innobase::general_fetch , which also respects the innodb_thread_concurrency parameter.

For deeper study, see the author’s book 深入理解 MySQL 主从原理 32 讲 and the referenced source‑code sections.