An efficient way to process a large number of rows with mysql_use_result() or streaming option
19 April 6:01 PM - 07:00 PM @ Ballroom B
Birds of a Feather
mysql_use_result() or mysql_store_result() is the call you make after mysql_query() call to indicate how you would like to retrieve the data. You may not recall that you have made the call or have indicate as such, that is because mysql_store_result() is the default. mysql_store_result() as the name suggests stores the whole result set into the client memory. This has two consequences: first of all it uses large amount of memory on the client side PROPORTIONAL to the number of rows to be retrieved, and it could be out of memory if there is no sufficient memory. Secondly, the client needs to wait for the complete result set to be retrieved before it can start to process even the first row. mysql_use_result() on the other hand just INITIATES the retrieval, and the client needs to ask one row at a time. The processing starts immediately, and the memory usage on the client side is MINIMAL and REGARDLESS of the number of rows involved. mysql_use_result() solved the problem of large memory usage due to the large number of rows to be retrieved, and because of this, eliminated the need for "pagination" or processing by batch which is utilized to overcome the memory burden.
MySQL DBA, Salesforce
Michael Wang is a MySQL DBA at Salesforce, he is fluent in Objected Oriented Programming in Perl.