PDO fetchall() performance considerations?

Question

After reading a few articles on the performance impact of PHP's PDO fetchall() function, I am curious if there is another way to accomplish the same outcome without invoking fetchall().

To explain, most agree that fetchall() tends to be resource intensive. In my case, I don't believe it will be too big of an issue. Yes, I am having to pull an entire table from my database and display it to the user, but it is going to be about a 100 rows at the very most; I don't believe that will be an issue. Hypothetically, though, if I were needing to pull 100,000 rows, what would be a more optimal solution?

Answer 1

Hypothetically, if you need to output all 100,000 rows in a single response, you should set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false, execute the query, and iterate the resultset one row at a time using fetch. To elaborate, php buffers the entire resultset regardless of whether or not you call fetchAll() if PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is set to true.

The benefit is that you drastically reduce the max memory consumption of the script, and you get to start streaming output sooner, although total time to completion may or may not take longer.

I'm ignoring other things you should consider in such extreme circumstances, like output buffering etc...

Answer 2

Alternative is to fetch one row after another inside a loop, like this:

while( $row = $statement->fetch() ){
    // so something with $row
}

Obviously, if you need all rows e.g. to calculate some statistics with them, then the above does not work. In many cases, though, SQL provides solutions that allow you to calculate such statistics on the fly, and return just the result.