MySQL full table scans: Handler_read_rnd_next and the missing index

You get paged because query latency is spiking. Slow_queries is climbing. Handler_read_rnd_next has jumped five-fold over its baseline and keeps rising. Connections, CPU, and buffer pool hit ratio look fine. The culprit is usually a new full table scan after a deploy, schema change, or query pattern shift.

Handler_read_rnd_next increments when the storage engine reads the next row during a table scan or sorted retrieval. In a healthy OLTP system, Handler_read_key dominates and Handler_read_rnd_next stays flat relative to query volume. When the ratio inverts, you are looking at a query plan regression, a missing index, or an optimizer decision gone wrong.

This guide targets OLTP MySQL with InnoDB. Batch jobs and ETL are expected to scan tables; the danger is an unexpected step-change during transactional workload.

What this means

Handler_read_rnd_next is a cumulative, monotonically increasing status counter that tracks sequential row reads. A full table scan of a one-million-row table adds roughly one million to this counter. It increments even when no rows satisfy the WHERE clause.

Handler_read_key tracks index-based lookups. When a query that previously used an index reverts to a table scan, you see a step-change in the rnd_next rate. If that correlates with rising Slow_queries, the scan is the problem.

The direct confirmation is EXPLAIN showing type = ALL and key = NULL, meaning the optimizer found no usable index. Other signals are Select_full_join and rising Sort_merge_passes when scan results are sorted on disk.

flowchart TD
    A[Handler_read_rnd_next step-change >5x] --> B{Rising Slow_queries?}
    B -->|Yes| C[New scan query appeared]
    B -->|No| D[Expected batch/ETL load]
    C --> E[EXPLAIN type=ALL]
    E --> F{Root cause}
    F --> G[Missing index]
    F --> H[Implicit type conversion]
    F --> I[Function on indexed column]
    F --> J[Stale statistics]
    F --> K[Missing replica index]

Common causes

CauseWhat it looks likeFirst thing to check
Missing or dropped indexHandler_read_rnd_next step-change after a deploy or migration; EXPLAIN shows type=ALLEXPLAIN output for key=NULL and possible_keys
Implicit type conversionAn index exists but EXPLAIN lists it in possible_keys while key is NULL; comparing a VARCHAR column to a numeric literalSHOW CREATE TABLE for column types versus query predicates
Function on an indexed columnQuery uses WHERE YEAR(created_at) = 2024; the index on created_at cannot be usedQuery text for functions wrapping column names
Stale optimizer statisticsA new index was added but the optimizer still chooses a scan; cardinality estimates are wrongSHOW INDEX cardinality or run ANALYZE TABLE
Missing index on a replicaReplica lag grows while the source is healthy; replica Handler_read_rnd_next is elevatedSHOW REPLICA STATUS and compare table indexes between source and replica
Sort buffer undersizedLarge ORDER BY or GROUP BY operations spill to disk; Sort_merge_passes rising alongside scansSHOW GLOBAL STATUS LIKE 'Sort_merge_passes'

Quick checks

Run these read-only checks to confirm scope before making changes.

-- Global scan activity versus keyed lookups
SHOW GLOBAL STATUS WHERE Variable_name IN ('Handler_read_rnd_next','Handler_read_key','Slow_queries');

-- Queries responsible for full table scans, ordered by total latency
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- Tables with the highest cumulative full-scan wait time
SELECT * FROM sys.schema_tables_with_full_table_scans LIMIT 10;

-- Joins without any index usage
SHOW GLOBAL STATUS LIKE 'Select_full_join';

-- Replica scan load and lag (8.0.22+)
SHOW REPLICA STATUS\G
SHOW GLOBAL STATUS LIKE 'Handler_read_rnd_next';

-- Per-query digest efficiency: rows examined versus rows sent
SELECT DIGEST_TEXT, SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
       ROUND(SUM_ROWS_EXAMINED/NULLIF(SUM_ROWS_SENT,0),2) AS examined_to_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

How to diagnose it

  1. Confirm the regression from baseline. A Handler_read_rnd_next step-change greater than 5x sustained for ten minutes, combined with rising Slow_queries, means a new scan query is active. Exclude expected batch windows.

  2. Isolate the offending query. Query sys.statements_with_full_table_scans to see which digests are scanning. Alternatively, query performance_schema.events_statements_summary_by_digest for digests with a high SUM_ROWS_EXAMINED to SUM_ROWS_SENT ratio. Focus on digests that are new or whose latency recently jumped.

  3. Verify the execution plan. Run EXPLAIN (or EXPLAIN ANALYZE ) on the suspect query. Look for type = ALL and key = NULL. If possible_keys lists an index but key is NULL, the optimizer judged the index too expensive, often due to low cardinality or stale statistics.

  4. Check for implicit conversion and function wrapping. Run SHOW CREATE TABLE and compare column types to query predicates. A VARCHAR compared to a numeric literal forces conversion and kills index usage. Look for SQL functions around indexed columns, such as YEAR(), DATE(), or CONCAT(), which prevent index access.

  5. Measure the per-query handler cost. In a dedicated diagnostic session, run:

    FLUSH STATUS;
-- run the suspect query here
SHOW SESSION STATUS LIKE 'Handler_read%';

    This resets the session counters, executes the query, and shows the delta. Handler_read_rnd_next will be roughly equal to the number of rows scanned.

  6. Check replicas if lag is present. If the source is healthy but replication lag is growing, compare indexes. A missing replica index causes row events to trigger table scans on the applier. Elevated Handler_read_rnd_next on the replica while the source is flat confirms it.

  7. Validate statistics. If an index exists but the optimizer ignores it, run ANALYZE TABLE tablename to refresh cardinality estimates. This acquires a read lock, so run it during a low-traffic window on large tables.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Handler_read_rnd_next ratePrimary indicator of full table scan volumeStep-change >5x baseline sustained for 10+ minutes
Handler_read_key rateHealthy index lookup baselineRatio to rnd_next collapsing or rnd_next exceeding it
Slow_queries rateConfirms scans are translating to user-visible latencyRising concurrently with Handler_read_rnd_next
Select_full_joinCatches joins with no usable indexAny sustained nonzero rate in OLTP
Innodb_rows_read / Com_selectRows examined per queryRatio climbing significantly above baseline
Sort_merge_passesSort buffer overflow forcing disk mergesRate rising alongside scan activity

Fixes

Add or restore the missing index

If EXPLAIN shows no usable index and the query filters on a column that supports it, add the index. Tradeoff: every additional index slows writes and consumes disk space. For write-heavy tables, prefer composite indexes that satisfy multiple queries over many single-column indexes.

Rewrite predicates to avoid functions on columns

If the query wraps an indexed column in a function, rewrite the predicate to a range. For example, replace WHERE YEAR(created_at) = 2024 with WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'. Tradeoff: requires application or stored procedure changes.

Fix implicit type conversion

When EXPLAIN lists an index in possible_keys but does not use it, check for type mismatches. Ensure the query passes string literals for VARCHAR columns and numeric literals for integer columns. Tradeoff: may require binding changes in the application or ORM layer.

Force an index hint for emergency relief

If a plan regression appeared suddenly and you need immediate relief, FORCE INDEX can revert to the previous plan. Tradeoff: hints override the optimizer and can make performance worse if data distribution changes later. Remove the hint once the root cause is fixed.

Run ANALYZE TABLE after index changes

After adding an index, the optimizer may still choose a scan if statistics are stale. ANALYZE TABLE tablename updates cardinality estimates. Tradeoff: acquires a read lock on the table for a short duration on large tables.

Add missing indexes on replicas

If a replica is missing an index that exists on the source, rebuild it. The replica will apply row events using the index and scan rates will drop. Tradeoff: replica write overhead increases, but that is usually preferable to unbounded lag.

Prevention

  • Track the scan-to-lookup ratio. A gradual shift toward Handler_read_rnd_next dominance is an early warning that data growth is outpacing your indexing strategy.
  • Monitor Select_full_join in OLTP. Any sustained nonzero rate means a join is missing an index; latency degrades as table size grows.
  • Lower long_query_time to 1 second or less. The default of 10 seconds hides plan regressions until they become severe enough to cause connection pile-up.
  • Review execution plans before deploys. A single unindexed WHERE clause on a large table can dominate server-wide scan counters.
  • Verify replica index parity in schema changes. Dropping an index on the source without checking replicas guarantees full scans on the applier and growing lag.

How Netdata helps

  • Netdata plots Handler_read_rnd_next and Handler_read_key as per-second rates, making step-changes visible without manual delta calculations.
  • Correlation charts show when Handler_read_rnd_next spikes alongside Slow_queries, Threads_running, or Select_full_join, shortening time to root cause.
  • Per-second resolution catches scan bursts that minute-granularity aggregations smooth over.
  • Netdata automatically baselines throughput signals like Questions and Innodb_rows_read, so a plan regression that raises rows examined per query stands out immediately.