Memory usage headfakes and inefficiencies

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