Unbuffered results?

[chowey]

How can I get Query to return an unbuffered Rows object, such as you get with mysql_use_result in the MySQL C-API?

[xaprb]

You cannot with the standard database/sql interface. I have a recollection that some other drivers (such as wrappers around the C libmysql) do allow this.

[chowey]

What about as a parameter in the connection string? I think I read that this is how the python driver works. The database/sql interface would allow this.

[methane]

go-sql-driver/mysql does like it already.
Query() returns Rows object. It doesn't have any rows.

    for rows.Next() {  // next row is readed here
        rows.Scan(...)
    }

See here for detail.

[chowey]

Sure, but that is a buffered response. In other words, MySQL on the server side built a temporary result set and is then feeding it to you one row at a time.

This is the mysql_store_result method of doing things, which is the normal mode of operation for MySQL. It is generally better for small result sets.

I have a specific use case for the mysql_use_result method of doing things.

[methane]

I hasn't know about server side difference between mysql_store_result and mysql_use_result.
How server know about buffered or unbuffered?

[chowey]

As far as I can tell, this mysql driver always uses the buffered result. That is the purpose of my question. @xaprb confirmed this is true.

Therefore this is more of a feature request. Can we support using an unbuffered result, possibly by passing a param to the connection string?

[methane]

As far as I know, both of mysql_use_result and mysql_store_result don't send any packet to server.
Server should not know about query is buffered or unbuffered.

[methane]

User of this driver can select buffered or unbuffered.

Buffered:

    for rows.Next() {
        var x X
        rows.Scan(&x)
        xs := append(xs, x)
    }
    rows.Close()
    for _, x := range xs {
        x.doSomething()
    }

Unbuffered:

    for rows.Next() {
        var x X
        rows.Scan(&x)
        x.doSomething()
    }
    rows.Close()

[chowey]

Sorry maybe I'm not being clear. I am not talking about buffering on the client (in your Go code). I am talking about how mysql buffers your result internally before sending it to you. This behavior can be changed, so that mysql doesn't buffer your result internally before sending it to you.

When mysql buffers its result, you will notice a delay before rows, err := db.Query("SELECT something") returns the rows struct.

When mysql does not buffer its result, you will notice that rows, err := db.Query("SELECT something") returns sooner but that there is now more delay on rows.Next().

I'm not talking about buffering your result in Go. I'm talking about what the mysql server is doing.

[methane]

mysql_use_result() and mysql_store_result() are called after mysql_real_query().
But there are no packet sent from client to server after query sent.
http://dev.mysql.com/doc/internals/en/com-query-response.html

How does server determine do or don't buffering?

[xaprb]

There is some confusion here that I will try to clarify. First, the
official MySQL C library, libmysql, which is wrapped by many other
libraries and thus exposes the same behaviors. It has the option to fetch
rows from the server as needed, or to fetch the whole resultset into a
buffer in the C library and then spool them from there. This is completely
client-side implementation and there is no protocol command or differences
from the server's point of view. The only thing the server does is send
over the socket as usual in both scenarios.

Go's database/sql itself has no such features as libmysql does. It does
row-at-a-time. The user of database/sql can fast-loop and store into a
slice as shown in an earlier comment, or loops and process one row at a
time. As the earlier commenter mentioned, that lets the user of the code
decide, and effectively puts buffering into the code that calls
rows.Next(), not inside database/sql.

Now, server-side buffering. There IS such a feature. But it is not
activated by the client-server protocol. It is activated by a SQL keyword,
see here: http://dev.mysql.com/doc/refman/5.0/en/select.html

The syntax is an optional hint keyword after SELECT, as documented:

  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

Please read the documentation for the details. This, again, is independent
of the protocol.

[shlomi-noach]

commenting on Baron's last comment on SQL_BUFFER_RESULT usage, a couple points worth noting:

• This claims memory on server side (instead of client side), then holds this memory active until the client is done processing the rows
• This only applies to SELECT queries, and does no apply to SHOW queries, some of which are common in MySQL operations and maintenance.