Enhancement request: Support buffered results

[shlomi-noach]

At this time, as I select a large number of rows from the database (e.g. 1,000,000) at a single query, and process those rows on application side, what locks are taken by MySQL for the query are kept until the application is done processing.
This is due to the go-mysql driver not buffering result sets.

I suggest that this should be controllable; similarly to the C driver or the Connector/J driver, there should be a mysql_use_result/mysql_store_result config to control buffering. I suggest this is actually an important feature as, unfortunately, MySQL happens to put locks on seemingly innocent queries.

Having a buffered result set would only lock throughout the network time for results delivery; by the time the application gets to handle the result set, any locks will have already been cleared, and the application is free to waste clock time without impacting other database users.

[shlomi-noach]

As a use case example, consider a case which leads to excessive locking. This particular case I consider to be a MySQL bug; nonetheless it is a use case, and I have encountered other use cases in the past. http://bugs.mysql.com/bug.php?id=76618

[xaprb]

See also #323.

[arnehormann]

More specific, see #323 (comment)
In short: Other drivers support this by buffering or not buffering on the client, there is no distinction between buffered and unbuffered queries in MySQL. We don't buffer - just process the data faster in your application so the connection can be released.

[shlomi-noach]

Might I suggest that processing the data faster in my application may not be fast enough: at best I will have to copy+paste a significant amount of memory before I can release the rowset. This in itself may take long enough.
I realize of course you don't buffer. This is a feature request asking you to buffer.

[shlomi-noach]

Continuing the argument, for sake of completeness, even extracting the type of an argument and translating to int etc. makes for extra cpu usage; cross-referencing my last comment from #323, the usage of SQL_BUFFER_RESULT is limited to SELECT queries and unsupported by SHOW queries; moreover it will incur additional memory taken on the server while the query iterates.

I understand that minimizing the processing time is desired; nonetheless I maintain that buffering the result set has value.

Keep up the good work

[arnehormann]

We can't include this - not sensibly.

First, we keep the driver as close to database/sql and database/sql/driver as we can. Both packages don't support a control over such a buffer. The feature could be controlled by the DSN - which would apply it to a whole connection pool and require another pool for unbuffered connections. Not great.

Second, putting this in would make the driver slower and more complex. It may be inconvenient sometimes, but we strive to keep it correct, fast, simple and predictable.

Third, we don't have any access to special magic. We'd have to buffer strings and convert them on access, end users know the types and can buffer themselves using a lot less memory. It would also require some more error handling and the potential of memory leaks.

That's the most important part - we can't do this faster and less memory intense than a user can - just the opposite in fact. As it would incur a penalty and always be a suboptimal solution, we won't do it.

[arnehormann]

Reopened by misclick...

[shlomi-noach]

It's Karma :)

Thank you