Percona Live: Data Performance Conference 2016 Logo

April 18-21, 2016

Santa Clara, California

An efficient way to process a large number of rows with mysql_use_result() or streaming option

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
Experience level: 
Intermediate
Duration: 
Birds of a Feather
Tracks:
Development
Scalability/Performance
Topics:
MySQL
Ruby
Programming

Description

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.

Speakers

Michael Wang's picture

Michael Wang

MySQL DBA, Salesforce

Biography:

Michael Wang is a MySQL DBA at Salesforce, he is fluent in Objected Oriented Programming in Perl.

Share this talk