MySQL select * from table without conditions does not return rows right away

MySQLwindows

I have a table with about 4 million rows and a bunch of columns on each row and I'm doing a simple select * from table in the mysql client and it doesn't return data right away. A count(*) of the same table returns a count in 3 seconds. I'm using the windows command line mysql client mysql.exe, running on AWS. I'm running a simple select * from the table created by the create table as select. No where clause conditions, no indexes, just a flat table with 4 million rows and a bunch of columns in a row. It seems like some sort of processing of the output is happening before it shows the first row. I'm new to MySQL so I'm trying to understand the behavior.

mysql> select count(*) from test;
--------------
select count(*) from test
--------------

+----------+
| count(*) |
+----------+
|  4074384 |
+----------+
1 row in set (2.96 sec)

mysql> select * from test;
--------------
select * from test
--------------

Does anyone know how to get the output to start streaming right away?

Edited version of create table as select:

create table test as
select 
pimxref.ap_ref,                            
...
        AND ipm.product_status = 'A'
--------------

Query OK, 4074384 rows affected (2 min 35.67 sec)
Records: 4074384  Duplicates: 0  Warnings: 0

Python script that runs the same query returns results instantly:

import mysql.connector
cnx = mysql.connector.connect(user='x', password='x',
                              host='x',
                              database='x')

cursor = cnx.cursor()

cursor.execute("select * from test")

for row in cursor:
    print(row)

cursor.close()
cnx.close()

If someone knows an option for the Windows mysql client to make it behave in the same way it would be great.

p.s. This is the version I am running:
Server version: 5.7.17-log MySQL Community Server (GPL)

Best Answer

What language are you using in your client? What connector? There may be a setting there.

Guessing that it is python, I see this:

https://dev.mysql.com/doc/connector-python/en/connector-python-api-cext-buffered.html

and guess that this would do the trick:

    cnx.buffered(True)

before the lengthy SELECT *. It is not likely to help SELECT COUNT(*) since the is nothing to 'buffer' until it is finished.

Since you added details about using the Windows cli mysql.exe utility, See if mysql --quick achieves your goal. If that doesn't provide the speed you require, perhaps consider using a loop, with a LIMIT clause, and "remember where you left off". This would make each call to mysql.exe relatively quick, at the expense of having to call it many times, once for each window of rows.

Related Question