9 Proven Tricks to Supercharge Your Pagination API Performance

Preface

A colleague asked how to ensure high performance for a million‑product pagination query API, prompting a deep dive into optimization strategies.

1 Increase default conditions

Provide sensible default values for query parameters to narrow the data range and avoid counting the entire table each time. For product queries, default to items uploaded today.

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

Reminder: add a composite index on the time and status fields.

2 Reduce page size

The pagination API typically receives pageNo (page number) and pageSize (items per page). If not supplied, default pageNo to 1 and pageSize to 10‑20. Avoid excessively large pageSize values, as they directly impact performance.

3 Reduce number of join tables

When the result requires joining many tables, first fetch only the primary keys (e.g., product IDs) and then retrieve related data in separate queries. This reduces the amount of data processed in the initial join.

select p.id, p.product_name, u.unit_name, b.brand_name, c.category_name from product p inner join unit u on p.unit_id = u.id inner join brand b on p.brand_id = b.id inner join category c on p.category_id = c.id where p.name='测试商品' limit 0,20;

After obtaining the 20 product IDs, fetch the auxiliary information in batch:

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(x -> x.getId().equals(product.getUnitId())).findAny();
    if (optional.isPresent()) {
        product.setUnitName(optional.get().getName());
    }
}

This approach dramatically cuts the number of joins and improves query speed.

4 Optimize indexes

Use the EXPLAIN statement to inspect the execution plan, verify that indexes exist and are used correctly, and create or adjust composite indexes as needed. Indexes should be added judiciously based on real‑world query performance.

5 Use straight_join

When MySQL chooses a suboptimal join order, replace INNER JOIN with STRAIGHT_JOIN to force the left table to drive the right table.

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

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

6 Data archiving

Archive historical data to a separate table, keeping only the most recent three months in the primary table. This reduces the data volume scanned by the pagination query while still allowing access to older records when needed.

7 Use count(*)

Different COUNT forms have varying performance. COUNT(*) and COUNT(1) are fastest, followed by COUNT(id), then counts on indexed columns, and finally counts on unindexed columns.

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

Therefore, always prefer COUNT(*) for total record counts.

8 Query from ClickHouse

For extremely large join workloads, offload data to ClickHouse, a column‑store database with sub‑second query times. Use Canal to capture MySQL binlog changes and sync new product data (including unit, brand, and category) into ClickHouse for fast analytics.

9 Database read/write splitting

When traffic grows, separate read and write traffic by directing writes to the master database and reads to one or more replica slaves. This reduces connection contention and improves pagination query latency.