Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Retrieving a MySQL query in chunks

by dannoura (Pilgrim)
on Sep 12, 2007 at 07:39 UTC ( #638503=perlquestion: print w/replies, xml ) Need Help??
dannoura has asked for the wisdom of the Perl Monks concerning the following question:


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?


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

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

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
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.


      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.
        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.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://638503]
Approved by Corion
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (10)
As of 2018-01-17 09:02 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (197 votes). Check out past polls.