Juan Morales Juan Morales - 1 year ago 108
SQL Question

Allowed memory size of 134217728 bytes exhausted + mysql + php

I have the following code that executes a SQL query with thousands of records:

ini_set('display_errors', 1);
ini_set('memory_limit', '2000M');
$dbquery = $data -> prepare("SELECT * FROM FILE ORDER BY ID");
$dbquery -> execute();
$data = $dbquery -> fetchAll(PDO::FETCH_ASSOC);
foreach($data as $rows):
$values[] = array('fecha' => $rows['UPDATED']

Edit on my /etc/php5/cli/php.ini and my /etc/php5/apache2/php.ini. but still does not show a record, which would you recommend? if you placed "LIMIT 10" in my query if I show some records, Greetings!

memory_limit = 9999M

Answer Source

When handling thousands of rows, it's usually more memory efficient to fetch each row individually instead of the entire resultset into memory. Right now, you're using fetchAll(...) to load all the records from FILE into $data and then you're looping through it. From what I can see, this is unnecessary and the same result can be achieved with less memory usage with the following code:

$dbquery = $data->prepare("SELECT * FROM FILE ORDER BY ID");
while($rows = $dbquery->fetch(PDO::FETCH_ASSOC)) {
    $values[] = array('fecha' => $rows['UPDATED']);

The change here is that a single record is only in memory for as long as the while loop needs it, which is generally really short and therefore much more memory efficient. This should allow for a much lower memory limit (i.e. max file size + a few MB).

Seeing as your table is called FILE I can imagine it contains files which can be of various sizes. Your while-loop contents currently do not show if you actually use the file-contents stored in your database (I presume), but if you don't it could be even more memory efficient to just select the columns you need. For instance:

$dbquery = $data->prepare("SELECT UPDATED FROM FILE ORDER BY ID");

Final word of advice is not to store files in the database but on the filesystem and only store a path to the files in your database. Most database engines (MySQL as well) aren't designed for file storage while your actual filesystem is, so using that will prove much more efficient.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download