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.

1. Is it true that, when using streaming, each row is fetched in a separate database roundtrip?

2. Does the MySQL server prefetches the result-set in advance or does it traverse the server-side cursor one row at a time too?

Answer 1

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.