CODEWITHSUNDEEP
javamysqljdbccursorresultset
Vlad Mihalcea
Vlad Mihalcea

Reputation: 153700

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

The MySQL JDBC connector defines two fetch modes:

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?

Upvotes: 2

Views: 586

Answers (1)

Morgan Tocker
Morgan Tocker

Reputation: 3438

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.

Upvotes: 1

Related Questions