MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:September 29 2009 8:19am
Subject:Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384
View as plain text  
Hi all,

So, there is a small benchmark based on the test case provided in the bug report:

create table t(a int not null auto_increment primary key,b text)engine=innodb;
insert into t (b) values ('a'),('b'),('c'),('d'),('e'),('f');
insert into t (b) select b from t;
...
insert into t (b) select b from t;
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  3145728 |
+----------+
1 row in set (5.21 sec)

# Constant cache enabled.

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (22.70 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (22.49 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (22.52 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (22.77 sec)
Rows matched: 0  Changed: 0  Warnings: 0

# Constant cache disabled.

mysql> update t set b='a' where a='999999999999';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 0 rows affected (23.85 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (24.94 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (23.06 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (25.04 sec)
Rows matched: 0  Changed: 0  Warnings: 0

The difference is about 7%.
If you think it doesn't worth to bother I'll close
it as "not a bug" with an advice to use CAST.

Regards, Evgen.

Øystein Grøvlen wrote:
> Evgeny Potemkin wrote:
>  > Hi,
>  >
>  > The bug#34384 is about UPDATE being too slow.
>  >
>  > See:
>  > create table t(a int primary key, ...);
>  > insert ...
>  > update ... where a='9999999999999999';
>  >
>  > As you can see, a is an INT, and the constant is a STRING.
>  > To correctly compare them we convert both to DOUBLE.
>  > Obviously, in this case the index over "a" can't be used.
>  > Currently left & right parts of comparison are converted immediately
>  > before comparison is done. This is done for each row. It's ok for a
>  > field, but the converted constant can be cached. This patch implements
>  > caching of constants.
>  > This allows to speedup a bit comparison on large record sets, but isn't
>  > noticeable on small ones.
> 
> Hi,
> 
> In my opinion, performance fixes needs to be justified by some
> quantitative measures. I do not think we should make the code more
> complex unless it can be shown that it gives significant performance
> benefits.
> 
> In this particular case, even if there is a significant performance
> improvements in itself, it will be insignificant compared to what the
> user could achieved by casting the string to an int.
> 
> Anyhow, I will leave it to Sergei to decide whether this is a desired
> change or not, and get back to you with my specific comments on the
> patch.
> 
>  > The bug shown by Sergey is about wrongly used index for the SELECT
>  > i.e. a different one, and will be addressed in a different bug report.
> 
> OK, Good.
> 
> -- 
> Øystein
> 
Thread
bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Evgeny Potemkin19 Sep
  • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Øystein Grøvlen25 Sep
    • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Evgeny Potemkin25 Sep
      • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Øystein Grøvlen28 Sep
        • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Sergei Golubchik28 Sep
        • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Evgeny Potemkin29 Sep
  • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Øystein Grøvlen4 Oct