I setup a simple test case that I could run both on MySQL through the mysql client and on an Oracle database through sqlplus. The test case builds a table with about 1 million rows and does select * from the table. In sqlplus on Oracle 12 the first row pops up immediately when the select * statement is run. In mysql it takes over 2 minutes for the first row to pop up after the select * statement is run.
I think that the mysql client must pull down the entire result set to figure out how to display the columns in the correct widths and sqlplus just dumps the rows out based on the column sizes without looking at the data.
Does anyone know if I am right about the way mysql processes large result sets and is there any way the make the mysql client behave more like sqlplus?
I brought this issue up on DBA Stack Exchange without really getting the answer to the question although people tried to be helpful. Here is that discussion:
https://dba.stackexchange.com/questions/208161/mysql-select-from-table-without-conditions-does-not-return-rows-right-away
Here is my test case:
create table test
(
a0 integer,
a1 integer,
a2 integer,
a3 integer,
a4 integer,
a5 integer,
a6 integer,
a7 integer,
a8 integer,
a9 integer
);
insert into test values
(
123456789,
123456789,
123456789,
123456789,
123456789,
123456789,
123456789,
123456789,
123456789,
123456789
);
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
select count(*) from test;
select * from test;
I'm running the MySQL test from an Amazon RDS instance using the mysql command line client on my laptop over my work VPN. I'm running the Oracle test from an on premises Oracle database over my work VPN over the internet. So, in both cases the output goes over the internet through my VPN.