Pagination of a MySQL query sorted by non-unique values benchmark.

PaginationTest.php

<?php
// Pagination of a MySQL query ordered by non-unique values benchmark:
// The normal way with limit/offset.
// Grabbing all the id's and then doing the queries w/ groups of ids.
// This uses the MySQL employee example DB: https://dev.mysql.com/doc/employee/en/
function get_time() {
    list($usec, $sec) = explode(' ', microtime());
    return ((float)$usec + (float)$sec);
}

// Our row limit.
$limit = 100;

$pdo = new PDO(
    'mysql:host=localhost:3307;dbname=employees',
    'root',
    '',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

echo "Starting normal pagination test...\n";
$startTime = get_time();
$innerRounds = 0;
$stmt = $pdo->query('SELECT COUNT(*) AS `cnt` FROM `employees`');
$rowCount = $stmt->fetchColumn();
$pages = ceil($rowCount / $limit);
echo "Rows: $rowCount\n";
echo "Pages: $pages\n";

for ($offset = 0; $offset < $rowCount; $offset += $limit) {
    $stmt = $pdo->prepare('SELECT * FROM `employees` ORDER BY `last_name` LIMIT :offset, :limit');
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as $row) {
        ++$innerRounds;
    }
}

$endTime = get_time();
echo "Inner Rounds: $innerRounds\n";
echo 'Normal Pagination: ' . ($endTime - $startTime) . " seconds\n";

echo "Starting id pagination test...\n";
$startTime = get_time();
$rounds = 0;
$innerRounds = 0;
$ids = [];
$stmt = $pdo->query('SELECT `emp_no` FROM `employees` ORDER BY `last_name`');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $ids[] = $row['emp_no'];
}

while ($currentIds = implode(',' , array_splice($ids, 0, 100))) {
    $stmt = $pdo->query("SELECT * FROM `employees` WHERE `emp_no` IN ($currentIds)");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as $row) {
        ++$innerRounds;
    }
}

$endTime = get_time();
echo "Inner Rounds: $innerRounds\n";
echo 'Id Pagination: ' . ($endTime - $startTime) . " seconds\n";