Bug #41293 user variables from remote hosts can be considerably slow

Description:
consider the asme query written as

1)
select * from hugetable where col9 = '2014-06-08 00:00:00';

2)
set @constant = '2014-06-08 00:00:00';
select * from hugetable where col9 = @constant;

We find that when executing from a local client the 2 two are almost equally fast.  When exectued from a remote client the latter can be considerably slower.

For describing this I use SQLyog becuase it (unlike command line) reveals approximate 'pure' exectution time. Refer to:
http://webyog.com/faq/content/8/147/en/how-shall-i-understand-the-_query-execution-time_-t...

How to repeat:
select count(*) from hugetable; -- returns '51261'

select * from hugetable where col9 = '2014-06-08 00:00:00';
/*
(619 row(s) returned)
Execution Time : 00:00:00:916 (typical value)
... */

set @constant = '2014-06-08 00:00:00';
select * from hugetable where col9 = @constant;
/*
(619 row(s) returned)
Execution Time : 00:00:01:145 /* typical value + 20% */
... */

-- now conneting to 'mysql' client on the same server through putty
-- here both statements 1 and 3 below will report 0.25 to 0.3 secs .. no consistent differnce if user varaible is used or not

select * from hugetable where col9 = '2014-06-08 00:00:00';
set @constant = '2014-06-08 00:00:00';
select * from hugetable where col9 = @constant;

But a user reports that with 1 mio+ rows it is much more significant
http://www.webyog.com/forums//index.php?showtopic=4399

He pastes the test case

set @Customer = '012345';
Select customer from IASCUSTOMER where customer = @Customer;
(2 row(s) returned)
Execution Time : 00:00:00:219

Transfer Time : 00:00:00:000
Total Time : 00:00:00:219
Select customer from IASCUSTOMER where customer = '012345';
(2 row(s) returned)
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:015
Total Time : 00:00:00:015

But also mentions that with specific examples diiference may be as big as a fraction of a second and +10 seconds.

Suggested fix:
First find out what is happening!

I wonder if the server copies the user variables around internally (from server space to thread space) for every row found while executing the query and if this should be necessary?

User uses server 4.1.21 and we tested on 4.1.20.  That is the option we have currently.

I hope to be able to invite user to take part of this discussion!

Thank you for a problem report. Please, send SHOW CREATE TABLE results for the table(s) used and EXPLAIN results for the queries with and without user variable.

Hi, 
I'm the User having the bug... 
I tried executing the Query from Mysql Query Browser and from SQLyog Enterprise. Both show the same behaviour - with the use of Variable the Select statement seems to use no Index! I tried in on my replication-DB Server(Mysql 4.1.22-standard) with less Ram (Slower)- on a table with 1.1 Million Rows... 
Using no variable: 
(34 row(s) returned)
Execution Time : 00:00:00:015

Using a variable in the same Statement: 
Execution Time : 00:00:52:234

This is over 52 Seconds slower!

And repeating the Select statement whith the Variable over and over, there is no faster answer-  Seems no Cache is being used...

I just executed a Select Statement on the same server and table with no index  being used, and this takes:
Execution Time : 00:01:03:500

Create Table Statement

Attachment: Create Statement.txt (text/plain), 10.23 KiB.

wiht my example 

EXPLAIN select * from hugetable where col9 = '2014-06-08 00:00:00';

/*
    id  select_type  table      type    possible_keys  key     key_len  ref       rows  Extra      
------  -----------  ---------  ------  -------------  ------  -------  ------  ------  -----------
     1  SIMPLE       hugetable  ALL     (NULL)         (NULL)   (NULL)  (NULL)   51311  Using where
*/

set @constant = '2014-06-08 00:00:00';
EXPLAIN select * from hugetable where col9 = @constant;

/*
    id  select_type  table      type    possible_keys  key     key_len  ref       rows  Extra      
------  -----------  ---------  ------  -------------  ------  -------  ------  ------  -----------
     1  SIMPLE       hugetable  ALL     (NULL)         (NULL)   (NULL)  (NULL)   51311  Using where
*/

(the table in this example has no indexes)

set @doc = '40803231';
Explain Select * from IASSALITEM where client = '00' and company = '01' and doctype = 'GR' and docnum = @doc ;
"1"	"SIMPLE"	"IASSALITEM"	"ref"	"IASSALITEM_10,IASSALITEM_4,IASSALITEM_5,IASSALITEM_6,IASSSALITEM_R1,CLIENT,IASSALITEM_CUST_MAT,IASSALITEM_3"	"IASSALITEM_5"	"7"	"const,const,const"	"392600"	"Using where"

Explain Select * from IASSALITEM where client = '00' and company = '01' and doctype = 'GR' and docnum = '40803231';
"1"	"SIMPLE"	"IASSALITEM"	"range"	"IASSALITEM_U1,IASSALITEM_1,IASSALITEM_10,IASSALITEM_4,IASSALITEM_5,IASSALITEM_6,IASSSALITEM_R1,CLIENT,IASSALITEM_CUST_MAT,IASSALITEM_3"	"IASSALITEM_U1"	"8"	\N	"42"	"Using where"

The Create Statement was too big, you ca find it under "Files"

Martin

btw: I wonder if 5.0/5.1 SHOW PROFILE can be used to get additional info about this!

Thank you for the report.

20% decrease verified as described.

Test used:

drop table if exists t1;

create table t1(f1 int not null auto_increment primary key, f2 text);

insert into t1 (f2) values('fdsfdsfdsfdsfsdfdsfdsfdsfdsfdsfdsfds');
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;
insert into t1 (f2) select f2 from t1;

delimiter |

drop procedure if exists p1|

create procedure p1(v1 INT)
begin
set @var = v1;
SET @x = 0;
REPEAT
select f1, f2 into @dummy1, @dummy2 from t1 where f1 = @var;
SET @x = @x + 1;
UNTIL @x > 10000 END REPEAT;
end
|

call p1(10)|

Results with 5.0.

Local:

mysql> call p1(10);
Query OK, 0 rows affected (2.98 sec)

mysql> call p1(10);
Query OK, 0 rows affected (2.97 sec)

mysql> call p1(10);
Query OK, 0 rows affected (2.97 sec)

mysql> call p1(10);
Query OK, 0 rows affected (2.96 sec)

Remote:

mysql> call p1(10);
Query OK, 0 rows affected (3.40 sec)

mysql> call p1(10);
Query OK, 0 rows affected (3.26 sec)

mysql> call p1(10);
Query OK, 0 rows affected (3.26 sec)

mysql> call p1(10);
Query OK, 0 rows affected (3.26 sec)

mysql> call p1(10);
Query OK, 0 rows affected (3.41 sec)

Results for TCP/IP and socket on the same machine are identical:

master [localhost] {msandbox} (test) >  call p1(10);
Query OK, 0 rows affected (0.57 sec)

master [localhost] {msandbox} (test) >  call p1(10);
Query OK, 0 rows affected (0.57 sec)

master [localhost] {msandbox} (test) >  call p1(10);
Query OK, 0 rows affected (0.56 sec)

master [localhost] {msandbox} (test) >  call p1(10);
Query OK, 0 rows affected (0.56 sec)

master [localhost] {msandbox} (test) >  call p1(10);
Query OK, 0 rows affected (0.56 sec)

For remote host results are larger:

mysql> call p1(10);
Query OK, 0 rows affected (0.79 sec)

mysql> call p1(10);
Query OK, 0 rows affected (0.62 sec)

mysql> call p1(10);
Query OK, 0 rows affected (0.62 sec)

mysql> call p1(10);
Query OK, 0 rows affected (0.62 sec)

mysql> call p1(10);
Query OK, 0 rows affected (2.39 sec)

Same if reduce using of user variables:

drop procedure if exists p2|

create procedure p2(v1 INT)
begin
declare x int;
declare dummy1, dummy2 varchar(255);
set @var = v1;
SET x = 0;
REPEAT
select f1, f2 into dummy1, dummy2 from t1 where f1 = @var;
SET x = x + 1;
UNTIL x > 10000 END REPEAT;
end
|

Local (both tcp and socket):

master [localhost] {root} (test) > call p2(10);
Query OK, 0 rows affected (0.52 sec)

master [localhost] {root} (test) > call p2(10);
Query OK, 0 rows affected (0.52 sec)

master [localhost] {root} (test) > call p2(10);
Query OK, 0 rows affected (0.52 sec)

Remote:

mysql> call p2(10);
Query OK, 0 rows affected (0.58 sec)

mysql> call p2(10);
Query OK, 0 rows affected (0.76 sec)

mysql> call p2(10);
Query OK, 0 rows affected (0.71 sec)

mysql> call p2(10);
Query OK, 0 rows affected (0.61 sec)

mysql> call p2(10);
Query OK, 0 rows affected (0.78 sec)

mysql> call p2(10);
Query OK, 0 rows affected (0.79 sec)

Same with index:

alter table t1 add index ind1(f2(100));

Local:

master [localhost] {root} (test) > call p1(10);
Query OK, 0 rows affected (0.58 sec)

master [localhost] {root} (test) > call p1(10);
Query OK, 0 rows affected (0.57 sec)

master [localhost] {root} (test) > call p1(10);
Query OK, 0 rows affected (0.57 sec)

master [localhost] {root} (test) > call p1(10);
Query OK, 0 rows affected (0.57 sec)

master [localhost] {root} (test) > call p2(10);
Query OK, 0 rows affected (0.52 sec)

master [localhost] {root} (test) > call p2(10);
Query OK, 0 rows affected (0.52 sec)

master [localhost] {root} (test) > call p2(10);
Query OK, 0 rows affected (0.52 sec)

Remote:

mysql> call p1(10);
Query OK, 0 rows affected (0.75 sec)

mysql> call p1(10);
Query OK, 0 rows affected (0.76 sec)

mysql> call p1(10);
Query OK, 0 rows affected (0.68 sec)

mysql> call p2(10);
Query OK, 0 rows affected (0.60 sec)

mysql> call p2(10);
Query OK, 0 rows affected (0.77 sec)

mysql> call p2(10);
Query OK, 0 rows affected (0.76 sec)

Additional tests:

create procedure p3(v1 INT)
begin
declare x int;
declare dummy1, dummy2 varchar(255);
SET x = 0;
REPEAT
select f1, f2 into dummy1, dummy2 from t1 where f1 = v1;
SET x = x + 1;
UNTIL x > 10000 END REPEAT;
end
|

drop procedure if exists p4|

create procedure p4(v1 INT)
begin
declare x int;
SET x = 0;
REPEAT
set @var='fdsfdsfdsfdsfsdfdsfdsfdsfdsfdsfdsfds';
SET x = x + 1;
UNTIL x > 10000 END REPEAT;
end
|

Local:

master [localhost] {root} (test) > call p3(10);
Query OK, 0 rows affected (0.52 sec)

master [localhost] {root} (test) > call p3(10);
Query OK, 0 rows affected (0.52 sec)

master [localhost] {root} (test) > call p3(10);
Query OK, 0 rows affected (0.52 sec)

master [localhost] {root} (test) > call p3(10);
Query OK, 0 rows affected (0.52 sec)

master [localhost] {root} (test) > call p4(10);
Query OK, 0 rows affected (0.17 sec)

master [localhost] {root} (test) > call p4(10);
Query OK, 0 rows affected (0.16 sec)

master [localhost] {root} (test) > call p4(10);
Query OK, 0 rows affected (0.17 sec)

master [localhost] {root} (test) > call p4(10);
Query OK, 0 rows affected (0.17 sec)

Same with tcp and socket

Remote:

mysql> call p3(10);
Query OK, 0 rows affected (0.66 sec)

mysql> call p3(10);
Query OK, 0 rows affected (0.72 sec)

mysql> call p3(10);
Query OK, 0 rows affected (0.73 sec)

mysql> call p4(10);
Query OK, 0 rows affected (0.23 sec)

mysql> call p4(10);
Query OK, 0 rows affected (0.24 sec)

mysql> call p4(10);
Query OK, 0 rows affected (0.23 sec)

Probably not a bug.  It's likely RTT times.
See also:
 https://bugs.mysql.com/bug.php?id=115646