Perl DBD::mysql 'mysql_use_result' OK to use while inserting to another table?

Question

I'm doing a large join in mysql, and then acting on the resulting data and writing it to another table. Right now, I'm using the default mysql_store_result, but the $sth->execute() after the query takes about 20 minutes to run. I'm wanting to switch over to mysql_use_result since I'm only acting on one row at a time.

But, this part of the documentation bothers me:

This attribute forces the driver to use mysql_use_result rather than mysql_store_result. The former is faster and less memory consuming, but tends to block other processes. (That's why mysql_store_result is the default.)

I'm acting on one row at a time, and then storing the result in an array. Every 500 entries, I do an INSERT into a separate table. Would this be OK to do from within the mysql_use_result loop? Would it be OK to do if I define a new DB handler for the INSERTs?

ysth · Accepted Answer · 2011-09-01 19:53:26Z

1

You should use a separate DB connection to do the inserts.

However, I believe the cautionary note you quote is talking about blocking things on the server, not just on your particular client.

The mysql documentation is a little clearer:

you shouldn't use mysql_use_result() if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This ties up the server and prevent other threads from updating any tables from which the data is being fetched.

(I believe that's an overstatement, and may have been written back when mysql was much more of a toy SQL implementation.)

answered Sep 1, 2011 at 19:53

ysth

99.1k6 gold badges126 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

coding_hero Over a year ago

The last line just makes me wonder even more. I'm not writing to the same table as I'm reading.

ysth Over a year ago

You aren't, but is anyone? In any case, there's stuff on that mysql page that makes it really clear you need a separate connection to do other stuff.

Collectives™ on Stack Overflow

Perl DBD::mysql 'mysql_use_result' OK to use while inserting to another table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related