Why MyBatis PageHelper Pagination Is Slow and How to Optimize It

In a project using MyBatis PageHelper for pagination, the author observed that a query which runs quickly directly in the database becomes extremely slow (over 8 seconds) when executed through the pagination plugin.

Reason : PageHelper executes select count(0) twice – first it runs the original query without any limit to build a temporary result set, then it runs the count again on that temporary set. This effectively scans the entire data set twice, which dramatically slows down pagination for large tables.

Example of the generated count SQL:

select count(*) from ("original query sql, equivalent to extra query") user

Full SQL printed by the plugin (simplified):

SELECT count(0)
FROM (
  SELECT V.*, B.TypeName AS TravelTypeName
  FROM (
    SELECT A.PKID, A.ApplyOrderNo FROM t1 AS A
    LEFT JOIN t2 AS P ON P.ApplyOrderNo = A.ApplyOrderNo
    LEFT JOIN t3 AS T ON A.UserCode = T.UserCode
  ) AS V
  LEFT JOIN t4 AS B ON V.TravelCode = B.TypeCode AND V.UserCode = B.UserCode AND B.Isdel = 0
)

Optimization方案 : Append _COUNT to the original mapper method name and provide a custom count query that returns only the total number, avoiding the extra full‑table scan.

Original mapper method:

Page<Map<String, Object>> getOrderInfo(Map<String, Integer> map);

Rewrite a new method with _COUNT suffix (return type must be Long ):

Long getOrderInfo_COUNT();

Corresponding XML definitions:

<select id="getOrderInfo" resultType="Map" useCache="false">
  SELECT V.*, B.TypeName AS TravelTypeName
  FROM (
    SELECT A.PKID, A.ApplyOrderNo FROM t1 AS A
    LEFT JOIN t2 AS P ON P.ApplyOrderNo = A.ApplyOrderNo
    LEFT JOIN t3 AS T ON A.UserCode = T.UserCode
  ) AS V
  LEFT JOIN t4 AS B ON V.TravelCode = B.TypeCode AND V.UserCode = B.UserCode AND B.Isdel = 0
</select>

<select id="getOrderInfo_COUNT" resultType="Long" useCache="false">
  SELECT COUNT(1) FROM t1
</select>

By overriding the default count SQL with the custom _COUNT method, the pagination query no longer creates a temporary table for the full data set, dramatically improving performance.

Summary : The default PageHelper count logic can be a bottleneck for large result sets; customizing the count query (using a method ending with _COUNT and returning Long ) resolves the issue.