So, for a few days I was testing how offset behaves with massive data sets (100+k rows) and it was terrible. Offset is insanely resource hungry and definitely not for the task if you have large number of rows in your database.
My final code looks like this (abstraction):
chunker.php
// Divide table rows into 50 chunks and produce array
$rows = DB::query("SELECT id FROM data_set GROUP BY id DESC");
$chunks = array_chunk($rows, 50, TRUE);
// Extract endings of each chunk array
$ends = array();
foreach ($chunks as $c) {
$f = flattenArray($c);
$arr_end = end($f);
array_push($ends, $arr_end);
}
// Spawn separate PHP processes to work with a chunk array ending
foreach ($ends as $e) {
$bb = shell_exec("php -q worker.php '".$e."' > /dev/null &");
}
worker.php
// Catch argv
$exec_args = $_SERVER['argv'];
// Select predefined amount of rows from DB which is more than or equal to argv value
$data = DB::query("SELECT * FROM data_set WHERE id >= %i LIMIT 50", $exec_args[1]);
foreach ($data as $d) {
// Do you stuff here
}
This adaptation came from this article http://mysql.rjweb.org/doc.php/pagination
Performance wise I offseting data required 8 CPU cores with 32 GB RAM. With LIMIT method I only need 4 GB RAM and 2 CPU's. So, think twice before using offset in LARGE data sets.