将单个数字除以范围

Question

I can't think a way in PHP to divide number into ranges.

I have massive MySQL database record set and want to spread some workload for my scripts.

Example:

There is 2435 rows in table, I want that each PHP script select only specific range of records:

Script 1: select [1 to 150];
Script 2: select [151 to 270];

The main problem: I can't think a method how to divide that 2435 into even ranges and pass them into MySQL SELECT.

Answer 1

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.