Bug #41293 | user variables from remote hosts can be considerably slow | ||
---|---|---|---|
Submitted: | 8 Dec 2008 10:21 | Modified: | 14 Apr 2009 19:11 |
Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0 bzr, 5.1 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | qc |
[8 Dec 2008 10:21]
Peter Laursen
[8 Dec 2008 12:08]
Valeriy Kravchuk
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.
[8 Dec 2008 12:10]
Martin Bruegemann
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
[8 Dec 2008 12:20]
Martin Bruegemann
Create Table Statement
Attachment: Create Statement.txt (text/plain), 10.23 KiB.
[8 Dec 2008 12:22]
Peter Laursen
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)
[8 Dec 2008 12:24]
Martin Bruegemann
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"
[8 Dec 2008 12:25]
Martin Bruegemann
The Create Statement was too big, you ca find it under "Files" Martin
[8 Dec 2008 13:09]
Peter Laursen
btw: I wonder if 5.0/5.1 SHOW PROFILE can be used to get additional info about this!
[2 Apr 2009 21:59]
Sveta Smirnova
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)
[14 Apr 2009 18:51]
Sveta Smirnova
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)
[14 Apr 2009 19:00]
Sveta Smirnova
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)
[14 Apr 2009 19:04]
Sveta Smirnova
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)
[14 Apr 2009 19:11]
Sveta Smirnova
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)
[14 Apr 2009 19:23]
Sveta Smirnova
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)