PHP's MySQL Cursor implementations and how they manage memory

Question

How do the different MySQL Cursors within PHP manage memory? What I mean is, when I make a MySQL query that retrieves a large result set, and get the MySQL resource back, how much of the data that the query retrieved is stored in local memory, and how are more results retrieved? Does the cursor automatically fetch all the results, and give them to me as I iterate through the resource with fetch_array or is it a buffered system?

Finally, are the cursors for the different drivers within mysql implemented differently? There's several MySQL drivers for PHP available, mysql, mysqli, pdo, etc. Do they all follow the same practices?

Answer 1

That depends on what you ask php to do, for instance mysql_query() grabs all the result set (if that's 500 megabytes, goodbye) ; if you don't want that you can use :

http://php.net/manual/en/function.mysql-unbuffered-query.php

PDO, MySQLI seem to have other ways of doing the same thing.

Depending on your query, the result set may be materialized on the database side (if you need a sort, then the sort must be done entirely before you even get the first row).

For not too large result sets it's usually better to fetch it all at once, so the server can free used resources asap.