How to Efficiently Paginate 10 Million MySQL Rows: Real‑World Tests & Optimizations

Introduction

This experiment evaluates pagination performance on a MySQL 5.7.26 table containing ten million rows. The focus is on the cost of large offsets and large result sets.

Data Preparation

A table user_operation_log is created with an auto‑increment primary key and many VARCHAR columns. A stored procedure inserts ten million rows in batches of 1,000, committing after each batch to keep the load fast.

CREATE TABLE `user_operation_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  /* attr2 … attr12 omitted for brevity */
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic;

DELIMITER ;; 
CREATE PROCEDURE batch_insert_log() 
BEGIN 
  DECLARE i INT DEFAULT 1; 
  DECLARE userId INT DEFAULT 10000000; 
  SET @execSql = 'INSERT INTO `user_operation_log`(`user_id`,`ip`,`op_data`,`attr1`,`attr2`,`attr3`,`attr4`,`attr5`,`attr6`,`attr7`,`attr8`,`attr9`,`attr10`,`attr11`,`attr12`) VALUES'; 
  SET @execData = ''; 
  WHILE i <= 10000000 DO 
    SET @attr = '"long attribute value"'; 
    SET @execData = CONCAT(@execData, '(', userId + i, ", '10.0.69.175', 'login', ", @attr, ',', @attr, ',', @attr, ',', @attr, ',', @attr, ',', @attr, ',', @attr, ',', @attr, ',', @attr, ')'); 
    IF i % 1000 = 0 THEN 
      SET @stmtSql = CONCAT(@execSql, @execData, ';'); 
      PREPARE stmt FROM @stmtSql; 
      EXECUTE stmt; 
      DEALLOCATE PREPARE stmt; 
      COMMIT; 
      SET @execData = ''; 
    ELSE 
      SET @execData = CONCAT(@execData, ','); 
    END IF; 
    SET i = i + 1; 
  END WHILE; 
END;; 
DELIMITER ;

Test Environment

Tests run on a Windows 10 laptop with a ~500 MB/s SSD. After inserting 3,148,000 rows (≈5 GB without indexes), a simple SELECT COUNT(1) takes ~13–14 seconds.

Basic Pagination Tests

Using LIMIT offset, rows:

SELECT * FROM `user_operation_log` LIMIT 10000, 10;

Three executions yielded 59 ms, 49 ms, and 50 ms, showing low latency for small offsets on a local instance.

Varying Result Size (Fixed Offset)

SELECT * FROM `user_operation_log` LIMIT 10000, 10;
SELECT * FROM `user_operation_log` LIMIT 10000, 100;
SELECT * FROM `user_operation_log` LIMIT 10000, 1000;
SELECT * FROM `user_operation_log` LIMIT 10000, 10000;
SELECT * FROM `user_operation_log` LIMIT 10000, 100000;
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000;

Execution time grows proportionally with the number of rows returned.

Varying Offset (Fixed Result Size)

SELECT * FROM `user_operation_log` LIMIT 100, 100;
SELECT * FROM `user_operation_log` LIMIT 1000, 100;
SELECT * FROM `user_operation_log` LIMIT 10000, 100;
SELECT * FROM `user_operation_log` LIMIT 100000, 100;
SELECT * FROM `user_operation_log` LIMIT 1000000, 100;

Time increases as the offset becomes larger, illustrating the classic “offset penalty”.

Optimizing Large Offsets

Subquery to Locate Starting ID

SELECT * FROM `user_operation_log` LIMIT 1000000, 10;
SELECT id FROM `user_operation_log` LIMIT 1000000, 1;
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10;

The subquery uses the primary‑key index, making the final query noticeably faster than a plain LIMIT with a large offset.

Some MySQL versions do not allow LIMIT inside an IN clause; a nested SELECT can be used instead.

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t);

ID‑Range Query (No Offset)

SELECT * FROM `user_operation_log` WHERE id BETWEEN 1000000 AND 1000100 LIMIT 100;
SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100;

Both statements execute in milliseconds because they avoid the offset cost entirely.

Optimizing Large Result Sets

Fetching fewer columns reduces latency. Three queries were compared:

SELECT * FROM `user_operation_log` LIMIT 1, 1000000;
SELECT id FROM `user_operation_log` LIMIT 1, 1000000;
SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000;

The id -only query was fastest; the explicit column list performed almost as well as SELECT *, confirming that omitting unnecessary columns improves performance.

Why Avoid SELECT *

The server must resolve metadata for every column, adding parsing overhead, especially in complex queries.

Transferring unneeded columns (e.g., large text or binary fields) inflates network traffic and can dominate payload size when client and server are separate.

Conclusion

Experiments confirm two primary pagination bottlenecks on massive tables: (1) large offsets, which cause the engine to scan many rows before returning results, and (2) large result sets, which increase I/O and network load. Using index‑based subqueries or ID‑range filters eliminates the offset penalty, and selecting only required columns minimizes data transfer. Applying these techniques can reduce query times from seconds to milliseconds on comparable workloads.