Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005

    Unanswered: Problem with selecting from a large table in Perl

    I have a large table with 4 million rows. I need to do an operation on each of the rows. I am using Perl.

    When I use the following code on a small table, it works just fine but on the large table, it gets stuck on line 3. As soon as it executes line 3, my hard drive starts going and keeps on going with no end. It never reaches line 4. My diagnosis follows the code fragment below:

    -------------------------------------code fragment begin
    1 my $sql = 'select myColumn from myTable';
    2 my $sth = $dbh->prepare($sql) || die("dbh prep failed");
    3 $sth->execute() || die("execute sth failed");
    4 my $colValue;
    5 $sth->bind_columns(\$colValue);
    6 while ($sth->fetch()) {
    7 do something to $colValue;
    8 }
    -------------------------------------code fragment end

    I think this is because mySQL is trying to load all 4 million rows into memory. Is there a way, perhaps a command line switch, to delay this so that it processes one row at a time. I have tried adding SQL_NO_CACHE to my SELECT statement but that did not make any difference at all.

    On a related note, when I was trying to export the four million rows using mysql command line, I had a similar problem but I found the -q command line switch for mysql command line (From the manual: -q or --quick means "Don't cache result, print it row by row.")

    I hope there is something similar for my Perl problem above too. Or am I doing something completely wrong in the way I am doing this? I am a newbie to mySQL.

  2. #2
    Join Date
    Apr 2005
    Lier, Belgium
    Quote Originally Posted by n827
    2 my $sth = $dbh->prepare($sql) || die("dbh prep failed");
    my $sth = $dbh->prepare($sql, { "mysql_use_result" => 1}) || die ("dbh prepare failed ");
    Read about mysql_use_result in the DBD::mysql documentation.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts