Testing MySQL Pagination Performance on Large Datasets

The author starts by answering an interview question about querying ten million rows, then explains that creating such a table directly is impractical and suggests using a fast database script.

Data preparation : a CREATE TABLE `user_operation_log` (...) statement defines a table with many varchar columns and an auto‑increment primary key.

Batch insert script (MySQL stored procedure) inserts ten million rows in batches of 1,000, committing after each batch:

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 ;

After inserting about 3.148 million rows (due to hardware limits), the author records three query times for counting rows (≈38 minutes) and three timings for a simple LIMIT query (≈50 ms each).

Ordinary pagination tests use MySQL's SELECT * FROM `user_operation_log` LIMIT offset, rows with varying offsets and row counts. Results show that larger offsets and larger result sets increase latency.

Offset‑optimization : the author demonstrates a sub‑query technique to locate the starting id and then fetch rows using WHERE id >= (SELECT id FROM ... LIMIT offset, 1) LIMIT rows , which leverages the primary‑key index and reduces time for large offsets. An alternative using IN (SELECT id FROM ... LIMIT ...) is provided for non‑incremental ids.

Another method restricts the query by id range with WHERE id BETWEEN start AND end or WHERE id >= start , which is extremely fast because it avoids offset scanning.

Large‑dataset optimization : the author shows that selecting fewer columns dramatically improves performance, comparing SELECT * with a column‑list query on one million rows. The conclusion is to avoid SELECT * and only retrieve needed fields.

Finally, the article wraps up with a reminder to practice these techniques, a call to share the post, and attribution to the original source.