Does MySQL use server-side pre-fetching when streaming a ResultSet

Question

The MySQL JDBC connector defines two fetch modes:

the default one fetches the whole ResultSet at once
streaming, when the statement fetchSize is set to Integer.MIN_VALUE

According to the documentation, the streaming will fetch each row individually, one at a time.

Is it true that, when using streaming, each row is fetched in a separate database roundtrip?
Does the MySQL server prefetches the result-set in advance or does it traverse the server-side cursor one row at a time too?

Isn't this a duplicate of http://stackoverflow.com/questions/35346229/does-mysql-connector-j-buffer-rows-when-streaming-a-resultset ? — Mark Rotteveel, Mar 12 '16 at 14:48
It could be, although that question actually contains two questions. — Vlad Mihalcea, Mar 12 '16 at 15:00

score 1 · Accepted Answer · answered Mar 12 '16 at 14:51

I believe the short answer is yes. I don't know the nuances as it applies to a mysql_use_result/mysql_store_result, but there are a few types of prefetch:

The InnoDB storage engine underneath has read-ahead, so it will start fetching pages in advance.
Some queries do need to be materialized in full before they can be streamed row at a time (think of a sort without using an index, or a group by without loose index scan). If this happens, the temporary table will show up using the show profiles feature.
Finally, in MySQL 5.6+ the retrieve from the storage engine can be batched (BKA). This is probably the case you were hinting at, the buffer that fills is called join_buffer_size.

Does MySQL use server-side pre-fetching when streaming a ResultSet

1 Answers1