Grabbing 190k rows from MySQL using PHP, blank page? Memory or buffer?

Question

I'm trying to grab all data from a table, to calculate some stats.

There is currently 190.000 rows and counting, and I am selecting only the columns necessary.

Using Kohana Query Builder, when I do execute()->count() (counts the rows only) on the MySQL query, it works fine and returns the number 190.000

When i try to fetch the data, by doing execute()->as_array(), it dies - it shows a blank page.

I tried researching to determine if it was a MySQL database limit/buffer limit, or PHP side - and I think its the PHP that has a limit? Am i wrong? I can fine grab what I need through the mysql console.

I run on a own and fast server, so this should really not be a problem - but I dont know which variables to configure (php settings)

My memory_limit is currently 128M
My max_execution_time is currently 800

What can I do?

Update:

The log says: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted

Answer 1

Putting Kohana aside, because I have never used it, what you're currently doing is basically this:

$result = mysql_query(...);
$data   = array();
while ($row = mysql_fetch_assoc($result)) {
    $data[] = $row;
}

I.e. you're getting all the data from MySQL with mysql_fetch_assoc and store them all in PHP's memory by pushing it into $data. That means PHP needs to have enough memory to store all data at once, which it hasn't.

What you want to do is fetch one result row from MySQL, do something with it, then move on to the next row without storing everything in memory at once:

$result = mysql_query(...);
while ($row = mysql_fetch_assoc($result)) {
    echo $row['foo'];
}

And no, please don't use the deprecated mysql_ API, it's just the largest common denominator example here. Also, there must be a better way to do whatever you want to do than trying to fetch and output 190,000 rows at once.