1

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)

  • The select count is fast. It is the select * that is slow. I think it may just be the way the mysql client works. – Bobby Durrett May 29 '18 at 20:21
  • What do you expect? it's 4 million rows. – Eric Sep 24 '18 at 22:19
  • It just works differently in Oracle and I am learning MySQL. – Bobby Durrett Sep 24 '18 at 22:23
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.