How to prevent fatal error of memory size exhaustion

Question

I am retrieving 10K records (having 10 columns with datatypes as datetime, text, int, varchar) using mysql_fetch_object and fetching all columns from each joined table in the query , which gave the memory size exhaustion fatal error, but then I amended the query to get only some specific columns which eliminated the error, saving me from drowning in the lake of errors.

The query is simply joining 3 tables, A, B, C as , SELECT * FROM A JOIN B ON ......JOIN C ON ...... WHERE.....

But I am still in the fear that if the no. of records or no. of columns increase, then the error would appear again....so what would be the complete solution of this? I read other posts on SO, and found that mysql_unbuffered_query should be used. But is there no solution if I use mysql_fetch_object? Also will unbuffered query take more time as it returns one row at a time?

Answer 1

MySQL is not the one running out of memory here, it's your PHP script so reducing the size of the SELECT is only a partial solution.

My guess is that you're doing something like:

$collect = array();
while (has_records) {
    $collect[] = fetch_record;
}
foreach ($collect as $row) {
    handle_record($row);
}

Of course $collect will get very big. See if you can avoid collecting all records: Read a record, handle it, clean up memory and do the next iteration.

while (has_records) {
    var $row = fetch_record();
    handle_record($row);
}

---

Iterator sample code:

class DB {
    function get_iterator($query) {
        $query_result = do_query($query);
        return new Iterator($query_result);
    }
}

class MyIterator {
    public function __construct($mysql_result) {
        $this->res = $mysql_result;
    }
    public function hasMore() {
        // do some counting
    }
    public function fetch() {
        return mysql_fetch($this->res);
    }
}

echo '<table>';
$it = $db->get_iterator("SELECT * FROM `items`");
while($it->hasMore()) {
    echo '<tr>';
    $row = $it->fetch();
    foreach ($row as $cell) {
        echo '<td>' . $cell . '</td>';
    }
    echo '</tr>';
}
echo '</table>';

You can even implements Iterator so you can foreach over $it directly, which is awesome.