While fetching many records at once is definitely more efficient than running a single query for each row to fetch, such an approach can potentially lead to an “out of memory” condition in libmysqlclient when using PHP’s mysql extension.
<?php
// connect to mysql
$connection = new mysqli('localhost', 'username', 'password', 'database');
// create table of 400 columns
$query = 'CREATE TABLE `test`(`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT';
for ($col = 0; $col < 400; $col++) {
$query .= ", `col$col` CHAR(10) NOT NULL"; } $query .= ');';
$connection->query($query);
// write 2 million rows
for ($row = 0; $row < 2000000; $row++) {
$query = "INSERT INTO `test` VALUES ($row"; for ($col = 0; $col < 400; $col++) {
$query .= ', ' . mt_rand(1000000000, 9999999999);
}
$query .= ')'; $connection->query($query);
}
?>
PHP Warning: mysqli::query(): (HY000/2013): Lost connection to MySQL server during query in file_name on line n
<?php
$totalNumberToFetch = 10000;
$portionSize = 100;
for ($i = 0; $i <= ceil($totalNumberToFetch / $portionSize); $i++) {
$limitFrom = $portionSize * $i; $res = $connection->query( "SELECT `x`,`y` FROM `test` LIMIT $limitFrom, $portionSize");
}
?>
https://www.toptal.com/php/10-most-common-mistakes-php-programmers-make