Performance Testing and Optimization of MySQL Pagination on Large Datasets

Introduction

The author describes a typical interview question about querying ten million rows and explains that the test will be performed on MySQL 5.7.26.

Data Preparation

Because inserting ten million rows row‑by‑row is impractical, a fast script is used to create the table and bulk‑insert data via a stored procedure.

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` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  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 `test`.`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 = "'测试很长很长的属性'";
    SET @execData = CONCAT(@execData, '(', userId + i, ", '10.0.69.175', '用户登录操作'", ',', @attr, ',', @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 Execution

On a low‑end Windows 10 i5 machine, 3,148,000 rows (≈5 GB) were inserted, taking about 38 minutes. Three count queries returned 3,148,000 rows, each taking roughly 13‑14 seconds.

Simple Pagination

Using MySQL’s LIMIT offset, rows syntax, three queries with LIMIT 10000, 10 took 59 ms, 49 ms, and 50 ms respectively.

Impact of Data Volume

Increasing the row count in the LIMIT clause (10, 100, 1 000, 10 000, 100 000, 1 000 000) showed a clear rise in execution time as the total data size grew.

Impact of Offset

Keeping the result size constant (10 rows) while moving the offset from 100 to 1 000 000 demonstrated that larger offsets dramatically increase query latency.

Optimization Strategies

Reducing Large Offsets

Instead of a huge offset, first locate the starting id with a sub‑query, then fetch the desired rows using WHERE id >= (SELECT id FROM … LIMIT offset,1). This leverages the primary‑key index and is much faster, though it assumes monotonically increasing IDs.

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;

When IDs are not strictly increasing, a nested IN (SELECT id FROM … LIMIT …) pattern can be used, albeit with slightly more complexity.

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

Another approach is to filter by an id BETWEEN start AND end range, which eliminates the offset entirely and yields the best performance.

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

Reducing Result Size

Fetching only required columns instead of * reduces parsing overhead and network traffic. Experiments showed noticeable speed gains when selecting a subset of fields.

Why Avoid SELECT *

Using SELECT * forces MySQL to resolve all column metadata and may inadvertently transmit large, unnecessary fields (e.g., logs or large text blobs), increasing CPU load and network bandwidth, especially when the client resides on a different machine.

Conclusion

The author encourages readers to reproduce the tests, experiment with the provided scripts, and apply the discussed optimizations to improve pagination performance on massive tables.