Re: mysql client may read the first record when read_buffer is full?

xiao feng wrote:
> Hi,
> Why MySQL get all result before send to client?
> In My engine, table1 has 1,000,000 records.
> when I use "select * from table1"
> I must wait for 10 min to get the first record.
> I think client may read the first record when read_buffer is full.
>
> If all records cached before sending to mysql client, there needs
> more memory.

You can handle it both ways, depending on the API function used to retrieve the results, mysql_store_result() transfers all results to the client before returning control back to the client, mysql_use_result() on the other hand only starts the retrieval process and result rows may be fetched one by one as they are produced by the server.

mysql_store_result() transfers results to the client as quickly as possible, so freeing server resources but taking client resources instead to buffer the full result first.

mysql_use_result() on the other hand only retrieves rows one by one so the server has to buffer results to be transfered to the client. The client can work on the results as soon as the first result row has been determined and only has to buffer one row at a time instead of the full result, but if the client processes rows slower than the server can deliver them it will block the server connection thread which has to buffer results until they can be transfered to the client

See also:

  http://dev.mysql.com/doc/refman/5.1/en/mysql-store-result.html   http://dev.mysql.com/doc/refman/5.1/en/mysql-use-result.html

If you are using another language API or Connector instead of the C library the names of these functions may differ or there might even be completely different means to control this ...

-- 
Hartmut Holzgraefe, Senior Support Engineer                            .
MySQL AB, www.mysql.com

Discover new MySQL Monitoring & Advisory features at:
http://www.mysql.com/products/enterprise/whats_new.html

Hauptsitz: MySQL GmbH, Radlkoferstr. 2, D-81373 München
Geschäftsführer: Kaj Arnö - HRB München 162140



-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com