MySQL Forums
Forum List  »  Performance

mysql client does not return rows right away on large result set
Posted by: Bobby Durrett
Date: May 31, 2018 05:42PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql client does not return rows right away on large result set
105
May 31, 2018 05:42PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.