title: Does MySQL Connector/J buffer rows when "streaming" a ResultSet?
body: <p>Based on my reading, I see that the way to stream a <code>ResultSet</code> in MySQL using the MySQL JDBC driver is these two commands:</p>
  
  <pre><code>stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
  stmt.setFetchSize(Integer.MIN_VALUE);
  </code></pre>
  
  <p>My question is could an expert clarify if streaming the ResultSet using above code returns one row to client, then go to server to fetch next row and so on (terribly inefficient) or whether it is smart enough to do buffered streaming like a <code>BufferedStreamReader</code>? If it does buffered streaming, how to set the buffer size?</p>
  
  <p>EDIT: From the <a href="http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html" rel="nofollow">doc</a>:</p>
  
  <blockquote>
    <p>The combination of a forward-only, read-only result set, with a fetch
    size of Integer.MIN_VALUE serves as a signal to the driver to stream
    result sets row-by-row. After this, any result sets created with the
    statement will be retrieved row-by-row.</p>
  </blockquote>
  
  <p>Does this mean that if I have 10M rows then there are 10M roundtrips to the server to get these rows? This is terribly inefficient. How can I stream the <code>ResultSet</code> but have it buffered so that I don't have to make so many roundtrips?  </p>
  
  <p>EDIT2: It seems MySQL does some buffering automatically when fetchSize is set to Integer.MIN_VALUE. In my test I was able to read more than 40M rows in less than 20 minutes using <code>setFetchSize(Integer.MIN_VALUE)</code>. This translates to about 30,000 rows per second. I don't know how big average row was but its hard to imagine 30,000 roundtrips per second. </p>
  
  <p>Also a separate question: what does MySQL do if the result set has more elements than the fetchSize?  e.g., result set has 10M rows and fetchSize is set to 1000. What happens then?</p>