Many mod_perl developers use MySQL as their preferred relational database server because of its speed. Depending on the situation, it may be possible to change the way in which the DBD::mysql driver delivers data. The two attributes mysql_use_result and mysql_store_result influence the speed and size of the processes.

You can tell the DBD::mysql driver to change the default behavior before you start to fetch the results:

my $sth = $dbh->prepare($query);
$sth->{"mysql_use_result"} = 1;

This forces the driver to use mysql_use_result rather than mysql_store_result. The former is faster and uses less memory, but it tends to block other processes, which is why mysql_store_result is the default.

Think about it in client/server terms. When you ask the server to spoon-feed you the data as you use it, the server process must buffer the data, tie up that thread, and possibly keep database locks open for a long time. So if you read a row of data and ponder it for a while, the tables you have locked are still locked, and the server is busy talking to you every so often. That is the situation with mysql_use_result.

On the other hand, if you just suck down the whole data set to the client, then the server is free to serve other requests. This improves parallelism, since rather than blocking each other by doing frequent I/O, the server and client are working at the same time. That is the situation with mysql_store_result.

As the MySQL manual suggests, you should not use mysql_use_result if you are doing a lot of processing for each row on the client side. This can tie up the server and prevent other threads from updating the tables.

If you are using some other DBD driver, check its documentation to see if it provides the flexibility of DBD::mysql in this regard.