9 Proven Techniques to Supercharge Pagination Query Performance

1 Increase Default Conditions

Provide default values for query parameters such as recent publish date and status to narrow the data range and avoid counting the entire table each time.

<code>select * from product 
where edit_date >= '2023-02-20' and edit_date < '2023-02-21' and status = 1</code>

Remember to add a composite index on the

edit_date

and

status

columns.

2 Reduce Page Size

Set sensible defaults for

pageNo

(1) and

pageSize

(10‑20). Avoid allowing excessively large page sizes, as they directly impact performance.

3 Reduce Number of Joined Tables

First query only the foreign‑key IDs, then fetch related data in separate queries to avoid heavy joins.

<code>select p.id, p.product_id, u.unit_id, b.brand_id, c.category_id 
from product 
where name='测试商品' 
limit 0,20;</code>

<code>select id, name from unit where id in (1,2,3);</code>

<code>List&lt;Product&gt; productList = productMapper.search(searchEntity);
List&lt;Long&gt; unitIdList = productList.stream().map(Product::getUnitId).distinct().collect(Collectors.toList());
List&lt;Unit&gt; unitList = UnitMapper.queryUnitByIdList(unitIdList);
for (Product product : productList) {
    Optional&lt;Unit&gt; optional = unitList.stream().filter(u -> u.getId().equals(product.getUnitId())).findAny();
    optional.ifPresent(u -> product.setUnitName(u.getName()));
}</code>

4 Optimize Indexes

Use

EXPLAIN

to examine execution plans, then create or adjust composite indexes as needed, prioritizing the most selective columns.

5 Use STRAIGHT_JOIN

Force the join order by replacing

INNER JOIN

with

STRAIGHT_JOIN

when MySQL chooses a sub‑optimal plan.

<code>select p.id from product p 
straight_join warehouse w on p.id = w.product_id;</code>

6 Archive Historical Data

Keep only recent three months in the primary table and move older records to a history table, reducing the amount of data scanned by pagination queries.

7 Use COUNT(*)

When counting total rows,

COUNT(*)

(or

COUNT(1)

) is the fastest because it avoids column‑level checks.

COUNT(*) ≈ COUNT(1) > COUNT(id) > COUNT(indexed column) > COUNT(unindexed column)

8 Query ClickHouse for Heavy Joins

Offload massive join‑heavy queries to ClickHouse, a column‑store database, and sync data via Canal listening to MySQL binlog. This can boost count queries by many times.

9 Implement Database Read‑Write Splitting

Separate read traffic to replica databases while writes go to the primary, increasing connection availability and reducing latency under high concurrency.