1

If I used MySQLdb or JDBC to issue the sql: select * from users to Mysql. Suppose the table has 1 billion records. Then how many rows would be returned by Mysql in one chunk/package. I mean Mysql won't transfer the rows one by one neither transfer all of the data just one time, right? So what's the default chunk/package size one internet transfer to the client please?

If I used server-side cursor then I should set the fetch size bigger than default chunk size for better performance, right please?

Jack
  • 5,540
  • 13
  • 65
  • 113
  • 2
    Possible duplicate of [JDBC Select batching/fetch-size with MySQL](http://stackoverflow.com/questions/24098247/jdbc-select-batching-fetch-size-with-mysql) – Mark Rotteveel Nov 06 '15 at 09:25

1 Answers1

2

The implementation notes of MySQL's JDBC API implementation points out, that by default the whole set will be retreived and stored in memory. So if there are 1 billion records they will be retreived. The limiting factor would probably be the memory of your machine.

So to sum up the size of the ResultSet retreived is depending on the JDBC implementation. For example Oracle's JDBC-Driver would only retreive 10 rows at a time and store them in memory.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
MikeVe
  • 1,062
  • 8
  • 13
  • you mean after 1 billion records retrieved then return to client or just one by one return to client by Mysql? – Jack Nov 05 '15 at 21:12
  • 1 billion records will be retreived and stored in the JVM's heap. – MikeVe Nov 06 '15 at 14:54