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.

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

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.

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

select id, name from unit where id in (1,2,3);

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

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.

select p.id from product p 
straight_join warehouse w on p.id = w.product_id;

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.