Why Your MySQL Pagination Is Slow and How Index Condition Pushdown Fixes It

Background

A high‑concurrency pagination query on the test_user table generated many slow‑query logs, causing CPU usage to rise and unstable response times.

Analysis and Diagnosis

The query scans a variable number of rows; the scan count correlates with execution time. The full SQL is:

select id, uuid, name, user_type, is_deleted, modify_date
from test_user
where is_deleted = 0
  and user_type = 0
  and id > 10000
  and id % 10 = 9
order by id
limit 500;

The plan uses a range scan on the primary key, estimating about 8.8 million rows. Because the filter conditions ( is_deleted and user_type) have low selectivity, the engine must scan many rows before finding matches.

Data Distribution Check

Aggregating is_deleted and user_type shows roughly uniform distribution for most values, but user_type = 2 appears only ~1 k rows, positioned at high id values. Sample queries reveal the id ranges for each combination, confirming uneven distribution.

Execution Tests

Running the query for different filter combos yields:

# Query_time: 0.012232  Rows_examined: 19507
# Query_time: 0.009549  Rows_examined: 20537
# Query_time: 0.009835  Rows_examined: 21037
# Query_time: 6.981938  Rows_examined: 17890145

The last case ( user_type = 2) scans 17 million rows because the matching ids are near the end of the primary‑key order.

Optimization Plan

Since the query filters on is_deleted and user_type, a composite index on these columns plus id can direct the engine to the relevant rows without full primary‑key traversal.

alter table test_user add index idx_isdeleted_usertype_id(is_deleted, user_type, id);

The index follows the ESR principle (equality → sort → range) and enables Index Condition Pushdown (ICP), allowing the storage engine to evaluate the WHERE clause using only the index.

Post‑Optimization Explain

explain select id, uuid, name, user_type, is_deleted, modify_date
from test_user
where is_deleted = 0 and user_type = 2 and id > 0 and id % 10 = 9
order by id
limit 500;

The plan now shows type=range with key=idx_isdeleted_usertype_id and Using index condition, scanning only 999 rows.

Performance Comparison

Before optimization: # Query_time: 6.981938 Rows_examined: 17890145 After optimization: # Query_time: 0.000884 Rows_examined: 100 Scanned rows dropped from 17 million to 100, and query time fell from ~7 seconds to sub‑millisecond.

Index Condition Pushdown (ICP) Details

ICP pushes evaluable parts of the WHERE clause into the storage engine.

When enabled, the engine reads only index entries that satisfy the pushed conditions, reducing I/O.

Applicable to InnoDB/MyISAM tables and access types range, ref, eq_ref, ref_or_null.

Not usable with virtual‑column indexes, sub‑queries, stored functions, or triggers.

ICP Toggle Test

SET optimizer_switch='index_condition_pushdown=off';
# Query_time: 0.008031  Rows_examined: 5043

SET optimizer_switch='index_condition_pushdown=on';
# Query_time: 0.002724  Rows_examined: 500

Enabling ICP reduces scanned rows by a factor of ten and improves execution time 2–3×.

Conclusion

Parallelizing a pagination query requires the underlying SQL to be highly efficient; evaluate efficiency by the ratio of scanned rows to returned rows.

Primary‑key range scans become costly when additional low‑selectivity filters are present.

Creating a targeted composite index that includes the filter columns and the primary‑key column enables ICP, dramatically improving performance.