Testing & Optimizing MySQL Pagination for Millions of Rows – Fast Tips

Preparing Data

First a table user_operation_log is created with an auto‑increment primary key id and many varchar columns to simulate a realistic log record.

CREATE TABLE `user_operation_log` (
  `id` int 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;

Data Generation Script

A stored procedure batch_insert_log() inserts 10 000 000 rows in batches of 1 000, committing after each batch to keep the operation fast.

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,',',@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 the script generated 3 148 000 rows (≈5 GB) and took about 38 minutes to insert.

Counting the rows: SELECT count(1) FROM `user_operation_log`; Result: 3 148 000.

Three plain LIMIT queries (offset 10 000, rows 10) took 14 060 ms, 13 755 ms and 13 447 ms respectively.

Standard Pagination

Using LIMIT 10000,10 the query executed in 59 ms, 49 ms and 50 ms, showing that small offsets are fast on a local database.

Impact of Data Volume

Running the same offset with increasing row counts demonstrates that execution time grows with the total data size.

Impact of Offset

Running the same limit with larger offsets shows the query becomes slower as the offset increases.

Optimization Strategies

Large Offset

Replace a huge offset with a sub‑query that finds the starting id, then query by id >= that_value. This leverages the primary‑key index and dramatically reduces execution time.

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

Alternative approaches include using IN with nested selects or a BETWEEN range when id values are continuous.

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

Large Data Sets

Select only the columns you really need instead of *. Reducing the column list cuts both parsing overhead on the server and network transfer size.

SELECT id, user_id, ip FROM `user_operation_log` LIMIT 10000,10;

Using SELECT * forces MySQL to read all columns, including large text fields, which increases CPU work and network traffic, especially when the client is on a different machine.

Therefore, avoid SELECT * in production queries and always specify the required columns.