in reply to DBI + 'SELECT *' - Memory Use Galore?

This is actually a feature of the mysql code :)

By default, the client bears the burden of handling result sets.

You can change this to place the burden on the server via:

$sth->{"mysql_use_result"} = 1;

[download]
This forces DBD::Mysql to use the mysql internal call mysql_use_result() rather than mysql_store_result().

There are some references to this:

Replies are listed 'Best First'.
Re^2: DBI + 'SELECT *' - Memory Use Galore?
by tadman (Prior) on Feb 18, 2001 at 10:51 UTC
    It is not surprising that this turned out to be a MySQL thing. I was actually expecting something along those lines, but after reading through the docs, and the FAQs, and what have you, all I could find was a list of people having the same problem, but no solutions were suggested that actually worked.

    Until kschwab's, that is.

    The 'mysql_use_result' flag is a DBD::mysql feature that switches methods. As the driver documentation points out, 'mysql_use_result()' is faster than the default 'mysql_store_result' and it uses less memory, but nothing comes without a price, and in this case, it's pretty stiff. You can't do anything with your database connection until you are finished with your query. It blocks them all with a pseudo-cryptic but insightful error message:      Commands out of sync; You can't run this command now Fortunately, that one is in the docs (20.4.10).

    So, what I have done is created a special DBI database handle for that one query, and one for the others so that I can still INSERT, DELETE and what have you, without waiting for the big 'SELECT *' to finish. At least I don't have to pay per connection!
[reply]
[d/l]

In Section Seekers of Perl Wisdom