This may or may not be a database question (not a perl question?), but I thought that the most wise monks could answer the question.

I need to write a perl script using the DBI module (mysql driver), and I will need to iterate over each and every record in a given table to perform some calculations. I though perhaps I should just go ahead and do something like

SELECT * FROM tablename
[download]

and then use $dbh->fetchrow_arrayref() to deal with each row of results at a time. However, there are thousands of records in the table right now, and when it's finally implemented, likely hundreds of thousands of records. Is this list of massive results stored in a temporary table someplace in the MySQL system, or is it stored in memory in the DBI object?

My goal is to be able to do these iterations over each and every record, but I don't want the entire results to be shoved into memory, resulting in possibly consuming a vast majority of the system's overall memory. It's not important that this script run very fast, but I don't want it to slow the entire system to a crawl.

Again, forgive me, but I'm not sure if this is a perl question or database question. Probably both.

Humbly yours,
Kozz

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.