How to Stream Large MySQL Query Results Without Running Out of Memory

By default MySQL retrieves the whole result set into memory before returning it, similar to Oracle's ALL_ROWS mode; this can cause memory overflow when the table is large.

To enable streaming results, you can:

When connecting via the MySQL console, add the -q option: mysql -h hostname -u root -p -q.

When using JDBC, add useCursorFetch=true to the connection URL.

In your Java code, create a forward‑only, read‑only statement and set the fetch size to Integer.MIN_VALUE:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
    java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The MySQL driver interprets Integer.MIN_VALUE as a signal to switch to streaming mode; it is not used as an actual fetch size value. With this setting, the driver retrieves rows one by one instead of loading the entire set into memory.

Refer to the MySQL documentation under “Resultset” for more details.