"Renaud" <renaudd@gmail.com> wrote:

> When I execute a query that returns a quite huge quantity of rows, it
> seems mysql sends ALL the data to the client before that client is able
> to loop over the result set.
>
> This acts the same with e.g Perl DBI's execute(), or PHP's
> mysql_query(), so this looks like it has something to do with the
> server's behaviour.
>
> I was wondering if there was a way with mysql to do some kind of
> "progressive fetch" so that I do not end up with a script that uses 3GB
> of memory? Is it actually something that one cannot change, that has to
> be changed in the server setup, or in the client?

This can be chosen for each single statement:

http://dev.mysql.com/doc/refman/5.0/...re-result.html
vs.
http://dev.mysql.com/doc/refman/5.0/...se-result.html

Both PHP and DBD have bindings to chose whether mysql_store_result()
or mysql_use_result() is used to retrieve a result set. The former is
the default because a table becomes read-locked as long as a result
set is being "used" by a client.

Beginning with 5.0 you can use server side cursers to materialize the
result set in the server (without locking the underlying table(s)).


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/