Retrieving a MySQL query in chunks

dannoura has asked for the wisdom of the Perl Monks concerning the following question:

hi,

Is there any way to retrieve a large (humongous even) reply from a MySQL query in chunks, so it doesn't freeze up the system?

thanks,
dan.

p.s. I mean besides storing it in a temporary table with unique ids from each record.

Comment on Retrieving a MySQL query in chunks

Replies are listed 'Best First'.
Re: Retrieving a MySQL query in chunks by stark (Pilgrim) on Sep 12, 2007 at 08:42 UTC
Maybe you are looking for the "mysql_use_result" attribute of DBD::mysql. Set it like this: `my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});` Otherwise the whole result is stored in memory (default "mysql_store_result"). Hope this helps	[reply] [d/l]
Re: Retrieving a MySQL query in chunks by johnlawrence (Monk) on Sep 12, 2007 at 08:34 UTC
I'm not sure if it's exactly what you want, but using LIMIT should do this. MySQL SELECT Syntax	[reply]
Re: Retrieving a MySQL query in chunks by eriam (Beadle) on Sep 12, 2007 at 11:02 UTC
What you probably want is fetching the data with asynchrounous calls. You should have a look a POE::Component::SimpleDBI on cpan. Thanks Eriam	[reply]
Re^2: Retrieving a MySQL query in chunks by perrin (Chancellor) on Sep 12, 2007 at 14:33 UTC
That doesn't really solve the problem. The forked process still ends up loading the entire result set into memory at once. The answer for large queries is to set mysql_use_result.	[reply]
Re^3: Retrieving a MySQL query in chunks by eriam (Beadle) on Sep 13, 2007 at 06:39 UTC
Sure it will load the stuff in memory (so yes he may need a lot of it), but the application won't freeze which is also what he was trying to achieve. Eriam	[reply]