Immediate partial results with mysql command line client

[Rossere]

I have a table with 100 million rows. I want to process one row at a time in no particular order. I don't want to load all the data into memory at once, and I want immediate partial results. To eliminate potential problems with the ORM framework I'm using, I thought I would test my ability to get immediate partial results using the mysql command line client, as follows:

mysql> select id from my_table \G

But the mysql client sits there for 75 seconds without printing anything, growing to a final size of 5.8 GB (reported by top), and then starts printing the results. While printing the results, the memory consumption of the mysql client process does not change (it remains at 5.8 GB).

During the first 75 seconds, using a second mysql session, I can see that my query is in the sending data state. When the client starts printing results, the process command (as reported by show processlist changes from Query to Sleep.

Why does the mysql interactive client wait to print results until all the results are in? This is tremendously wasteful of RAM, and fails to yield immediate partial results.

[charlenef]

Invoke the MySQL client with the --quick option.

See https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_quick

--quick, -q

Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file.

The reason the default is to store the result in the client is implied by this documentation. If the interactive client is suspended (for example using Ctrl-Z job control), the MySQL Server must use resources to keep the result set active.

You should consider not fetching 100 million rows in a single query to the interactive client.