Why Physical Pagination Uses LIMIT and Its Performance Implications in MySQL

Before discussing LIMIT, we first introduce pagination, distinguishing logical pagination (similar to logical deletion) from physical pagination (actual removal of rows).

Logical pagination consumes more memory; for example, a MEMORY table vote_record_memory with millions of rows is shown, and after inserting three million records the dump file becomes huge.

We generally recommend using physical pagination and understanding the LIMIT syntax: LIMIT X, Y skips the first X rows and returns the next Y rows, where X is the offset and Y is the maximum number of rows.

Example query:

SELECT
    cue.real_name empName,
    zs.push_money AS pushMoney,
    zs.push_money_note AS pushMoneyNote,
    zs.create_datetime AS createTime
FROM zq_salary zs
LEFT JOIN core_user_ext cue ON cue.id = zs.user_id
WHERE zs.is_deleted = 0
  AND (cue.real_name LIKE '%李%' OR zs.push_money_note LIKE '%测%')
ORDER BY zs.create_datetime DESC
LIMIT 2;

Performance comparison:

Method 1 uses a plain LIMIT 3600000,20000 on the table; Method 2 adds a WHERE id >= 3600000 condition with the same LIMIT. Method 2 runs roughly one‑ninth the time of Method 1 because the offset in Method 1 forces a full table scan of 4 million rows, while Method 2 leverages the index and scans only about 48 k rows.

EXPLAIN output confirms that Method 1 scans all rows, whereas Method 2 uses the index (type range) and scans far fewer rows. This demonstrates why large offsets degrade performance and why using indexes is essential, especially for tables with millions of rows.

MySQL supports BTREE and HASH indexes; HASH is faster but only works for equality and IN queries, not range queries, so BTREE is preferred for pagination.

Physical pagination with LIMIT typically uses two parameters: X (offset) and Y (row count). Example page queries:

-- First page
SELECT * FROM vote_record_memory LIMIT 0,20;
-- Second page
SELECT * FROM vote_record_memory LIMIT 20,20;
-- Third page
SELECT * FROM vote_record_memory LIMIT 40,20;
-- Nth page
SELECT * FROM vote_record_memory LIMIT (n-1)*20,20;

A simple Java helper method illustrates how to build such a query dynamically:

/**
 * @description Simple pagination prototype
 * @author zby
 * @param currentPage Current page number
 * @param lines       Number of rows per page
 * @return List of data objects
 */
public List<Object> listObjects(int currentPage, int lines) {
    String sql = "SELECT * from vote_record_memory LIMIT " + (currentPage - 1) * lines + "," + lines;
    return null; // execute the query and return results
}

In summary, avoid logical pagination for large datasets, prefer physical pagination with indexed WHERE clauses, and use BTREE indexes for range queries to achieve optimal performance.