How to Boost Pagination Queries for a Million Products by 10×

1. Add Default Conditions

Provide sensible default values for query parameters, such as filtering by today's edit_date range and status=1, to shrink the data set before counting. Example:

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

Tip: Add a composite index on edit_date and status to let the filter use the index.

2. Reduce Page Size

Set reasonable defaults for pageNo (1) and pageSize (10‑20). Avoid large page sizes because they increase the amount of data scanned and transferred. Front‑end dropdowns can offer 10, 20, 50, 100 as options, but the default should stay small.

3. Reduce Number of Joins

Instead of joining unit, brand, and category in a single query, first fetch only the primary keys, then retrieve related data in separate queries. Original join example:

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;

Optimized two‑step approach:

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

Then fetch names by primary‑key lists, e.g.: select id, name from unit where id in (1,2,3); Pseudo‑code:

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

4. Optimize Indexes

Use EXPLAIN to inspect the execution plan, verify that useful indexes exist, and create composite indexes where appropriate. Prefer composite indexes over many single‑column indexes, and test their impact in production before committing.

5. Use STRAIGHT_JOIN

When MySQL chooses a sub‑optimal join order, replace INNER JOIN with STRAIGHT_JOIN to force left‑to‑right table driving. Example before:

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

After applying:

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

6. Data Archiving

Move older records (e.g., beyond three months) to a history table, keeping the main table small. The pagination query then reads only recent data, dramatically reducing the scanned rows.

7. Prefer COUNT(*)

Different count forms have varying performance. The ranking from fastest to slowest is:

COUNT(*) ≈ COUNT(1) > COUNT(id) > COUNT(indexed_column) > COUNT(non_indexed_column)

Therefore, always use COUNT(*) for total row counts.

8. Offload to ClickHouse

For massive read‑only analytics, replicate product data into ClickHouse, a column‑store that can return billions of rows in seconds. Use Canal to capture MySQL binlog changes and batch‑insert into ClickHouse. Queries against ClickHouse with COUNT(*) can be many times faster.

Note: Insert data into ClickHouse in batches; avoid very frequent single‑row inserts.

9. Database Read‑Write Splitting

When read traffic grows, separate reads to replica(s) and writes to the primary. Configure one‑master‑multiple‑slaves as needed. This reduces contention on the primary and improves pagination response times.