mysqli_query single query gerenating PHP fatal error: Allowed memory size exhausted

Question

I have a Mysql table (named "base") with 3 million records, which is composed of only 3 int(10) fields.

When I try to run this simple query $result = mysqli_query($db, 'SELECT * FROM base'), php shows the error:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 10485768 bytes) in C:\xampp\htdocs\combina.php on line 17

I think mysqli_query should not load all the table records into memory, right?

So why the memory overflow?

I'm using XAMPP installation on Windows 10, PHP 7.2.

Here is the code:

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "comb";

$db = mysqli_connect($servername, $username, $password, $dbname);
if (!$db) {
    die("Falha na conexão: " . mysqli_connect_error());
}
$result = mysqli_query($db, 'SELECT * FROM base');

The only code in the file is $result = mysqli_query($db, 'SELECT * FROM base')? — user3783243
The error occurs precisely on the line mysqli_query($db, 'SELECT * FROM base'). I did not understand your question. — Rogério Dec

Rogério Dec Rogério Dec · Accepted Answer · 2018-07-27T14:07:04

I researched and realized that the mysqli_query command actually loads ALL records into RAM. That is, for large tables, it will actually generate a memory limitation.

The solution I found was to use the combination of mysqli_real_query (which will activate the query without loading all the records in RAM, mysqli_use_result (which will return the resulting object not buffered ) and finally mysqli_fetch_row which will read the result line by line.

In this way the solution to the presented problem would be more or less:

$db = mysqli_connect($servername, $username, $password, $dbname);
mysqli_real_query($db, 'SELECT * FROM base');
$result = mysqli_use_result($db);
while ($row = mysqli_fetch_row($result)) {
    ...

mysqli_query single query gerenating PHP fatal error: Allowed memory size exhausted

1 Answers