Advice on returning results from a large query returning error- Allowed memory size of 734003200 bytes exhausted

Question

I'm trying to return the result of 3 tables being joined together for a user to download as CSV, and this is throwing the error:

Allowed memory size of 734003200 bytes exhausted

This is the query being run:

SELECT *
FROM `tblProgram`
JOIN `tblPlots` ON `tblPlots`.`programID`=`tblProgram`.`pkProgramID`
JOIN `tblTrees` ON `tblTrees`.`treePlotID`=`tblPlots`.`id`

The line of code causing the error is this:

$resultsALL=$this->db->query($fullQry);

Where $fullQry is the query shown above. When I comment out that single line, everything runs without the error. So I'm certain its not an infinite loop somewhere I'm missing.

I'm wondering how do I break up the query so that I can get the results without erroring out? The tables only have a relatively small amount of data in them right now and will be even larger eventually, so I don't think raising the memory size is a good option.

I'm using CodeIgniter/php/mysql. I can provide more code if need be...

Thank you for any direction you can advise!

Answer 1

Use getUnbufferedRow() for processing large result sets.

getUnbufferedRow()

This method returns a single result row without prefetching the whole result in memory as row() does. If your query has more than one row, it returns the current row and moves the internal data pointer ahead.

$query = $db->query("YOUR QUERY");

while ($row = $query->getUnbufferedRow()) {
    echo $row->title;
    echo $row->name;
    echo $row->body;
}

For use with MySQLi you may set MySQLi’s result mode to MYSQLI_USE_RESULT for maximum memory savings. Use of this is not generally recommended but it can be beneficial in some circumstances such as writing large queries to csv. If you change the result mode be aware of the tradeoffs associated with it.

$db->resultMode = MYSQLI_USE_RESULT; // for unbuffered results

$query = $db->query("YOUR QUERY");

$file = new \CodeIgniter\Files\File(WRITEPATH.'data.csv');

$csv = $file->openFile('w');

while ($row = $query->getUnbufferedRow('array'))
{
    $csv->fputcsv($row);
}

$db->resultMode = MYSQLI_STORE_RESULT; // return to default mode

Note:

When using MYSQLI_USE_RESULT all subsequent calls on the same connection will result in error until all records have been fetched or a freeResult() call has been made. The getNumRows() method will only return the number of rows based on the current position of the data pointer. MyISAM tables will remain locked until all the records have been fetched or a freeResult() call has been made.

You can optionally pass ‘object’ (default) or ‘array’ in order to specify the returned value’s type:

$query->getUnbufferedRow();         // object
$query->getUnbufferedRow('object'); // object
$query->getUnbufferedRow('array');  // associative array

freeResult()

It frees the memory associated with the result and deletes the result resource ID. Normally PHP frees its memory automatically at the end of script execution. However, if you are running a lot of queries in a particular script you might want to free the result after each query result has been generated in order to cut down on memory consumption.

$query = $thisdb->query('SELECT title FROM my_table');

foreach ($query->getResult() as $row) {
    echo $row->title;
}

$query->freeResult(); // The $query result object will no longer be available

$query2 = $db->query('SELECT name FROM some_table');

$row = $query2->getRow();
echo $row->name;
$query2->freeResult(); // The $query2 result object will no longer be available

Answer 2

Based off of: MySQL : retrieve a large select by chunks

You may also try retrieving the data in chunks by using the LIMIT clause.

Since you're using CodeIgniter 3, here is how you can go about it.

You may need to pass a different $orderBy argument#6 to the getChunk(...) method if in case your joined tables have conflicting id column names.

I.e: $this->getChunk(..., ..., ..., 0, 2000, "tblProgram.id");

Solution:

<?php

class Csv_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    public function index()
    {
        $sql = <<< END
SELECT *
FROM `tblProgram`
JOIN `tblPlots` ON `tblPlots`.`programID`=`tblProgram`.`pkProgramID`
JOIN `tblTrees` ON `tblTrees`.`treePlotID`=`tblPlots`.`id`
END;

        $this->getChunk(function (array $chunk) {
            /*
             * Do something with each chunk here;
             * Do something with each chunk here;
             * log_message('error', json_encode($chunk));
             * */
        }, $this->db, $sql);
    }

    /*
     * Processes a raw SQL query result in chunks sending each chunk to the provided callback function.
     * */
    function getChunk(callable $callback, $DBContext, string $rawSQL = "SELECT 1", int $initialRowOffset = 0, int $maxRows = 2000, string $orderBy = "id")
    {
        $DBContext->query('DROP TEMPORARY TABLE IF EXISTS chunkable');
        $DBContext->query("CREATE TEMPORARY TABLE chunkable AS ( $rawSQL ORDER BY `$orderBy` )");

        do {

            $constrainedSQL = sprintf("SELECT * FROM chunkable ORDER BY `$orderBy` LIMIT %d, %d", $initialRowOffset, $maxRows);
            $queryBuilder = $DBContext->query($constrainedSQL);
            $callback($queryBuilder->result_array());
            $initialRowOffset = $initialRowOffset + $maxRows;

        } while ($queryBuilder->num_rows() === $maxRows);
    }

}