Hi Timour, On Fri, Nov 18, 2011 at 11:16:27PM +0200, Timour Katchaounov wrote: > Sergey, > > Please review the following patch. > Do you have any idea about the change in > func_compress.result ? I've seen that before, > but don't remember what was the issue.
I think in this case it is fairly clear - a warning is produced every time a constant expression is evaluated. Your patch makes one less evaluation, so one fewer warning is emitted. The patch is ok to push after the below notes are addressed. > ------------------------------------------------------------ > revno: 3296 > revision-id: [email protected] > parent: [email protected] > fixes bug(s): https://launchpad.net/bugs/833777 > committer: [email protected] > branch nick: 5.3-lpb833777 > timestamp: Fri 2011-11-18 22:19:20 +0200 > message: > Fix bug lp:833777 > > Analysis: > The optimizer distinguishes two kinds of 'constant' conditions: > expensive ones, and non-expensive ones. The non-expensive conditions > are evaluated inside make_join_select(), and if false, already the > optimizer detects empty query results. > > In order to avoid arbitrarily expensive optimization, the evaluation of > expensive constant conditions is delayed until execution. These conditions > are attached to JOIN::exec_const_cond and evaluated in the beginning of > JOIN::exec. The relevant execution logic is: > > JOIN::exec() > { > if (! join->exec_const_cond->val_int()) > { > produce an empty result; > stop execution > } > continue execution > execute the original WHERE clause (that contains exec_const_cond) > ... > } > > As a result, when an expensive constant condition is > TRUE, it is evaluated twice - once through > JOIN::exec_const_cond, and once through JOIN::cond. > When the expensive constant condition is a subquery, > predicate, the subquery is evaluated twice. If we have > many levels of subqueries, this logic results in a chain > of recursive subquery executions that walk a perfect > binary tree. The result is that for subquries with depth N, > JOIN::exec is executed O(2^N) times. > > Solution: > Notice that the second execution of the constant conditions > happens inside do_select(), in the branch: > if (join->table_count == join->const_tables) { ... } > In this case exec_const_cond is equivalent to the whole WHERE > clause, therefore the WHERE clause has already been checked in > the beginnig of JOIN::exec, and has been found to be true. > The bug is addressed by not evaluating the WHERE clause if there > was exec_const_conds, and it was TRUE. > === modified file 'mysql-test/r/func_compress.result' > --- a/mysql-test/r/func_compress.result 2011-02-03 15:00:28 +0000 > +++ b/mysql-test/r/func_compress.result 2011-11-18 20:19:20 +0000 > @@ -103,7 +103,6 @@ foo > Warnings: > Error 1259 ZLIB: Input data corrupted > Error 1259 ZLIB: Input data corrupted > -Error 1259 ZLIB: Input data corrupted > explain select *, uncompress(a) from t1; > id select_type table type possible_keys key key_len ref > rows Extra > 1 SIMPLE t1 system NULL NULL NULL NULL 1 > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect.result 2011-11-18 20:19:20 +0000 > @@ -5665,4 +5665,14 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set > @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from > t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a > +0 Why use 'benchmark' function? It seems totally redundant. Please change the test to look like: select (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in ... (select a from t1) ))))))))))))))))))))))))))))))))) AS foo; to make it less cluttered. > +drop table t1; > set optimizer_switch=@subselect_tmp; > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2011-11-18 20:19:20 +0000 > @@ -5670,6 +5670,16 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set > @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from > t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%materialization=on%'; > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2011-11-18 20:19:20 +0000 > @@ -5666,5 +5666,15 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set > @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from > t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2011-11-18 20:19:20 +0000 > @@ -5669,6 +5669,16 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set > @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from > t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%subquery_cache=on%'; > > === modified file 'mysql-test/r/subselect_no_semijoin.result' > --- a/mysql-test/r/subselect_no_semijoin.result 2011-11-15 21:03:00 > +0000 > +++ b/mysql-test/r/subselect_no_semijoin.result 2011-11-18 20:19:20 > +0000 > @@ -5666,5 +5666,15 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set > @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from > t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/t/subselect.test' > --- a/mysql-test/t/subselect.test 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/t/subselect.test 2011-11-18 20:19:20 +0000 > @@ -4768,4 +4768,14 @@ SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > > +--echo # > +--echo # LP BUG#833777 Performance regression with deeply nested subqueries > +--echo # > + > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set > @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from t1 where a in > (select a from t1 where a in (select a from t1 where a in (select a from t1 > where a in (select a from t1 where a in (select a from t1 where a in (select > a from t1 where a in (select a from t1 where a in (select a from t1 where a > in (select a from t1 where a in (select a from t1 where a in (select a from > t1 where a in (select a from t1 where a in (select a from > t1)))))))))))))))))))))))))))))))))); > +drop table t1; > + > set optimizer_switch=@subselect_tmp; > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2011-11-15 21:03:00 +0000 > +++ b/sql/sql_select.cc 2011-11-18 20:19:20 +0000 > @@ -14782,9 +14782,15 @@ do_select(JOIN *join,List<Item> *fields, > { > /* > HAVING will be checked after processing aggregate functions, > - But WHERE should checkd here (we alredy have read tables) > + But WHERE should checkd here (we alredy have read tables). > + If there is join->exec_const_cond, and all tables are constant, then it > + is equivalent to join->conds. exec_const_cond is already checked in the > + beginning of JOIN::exec. If it is false, JOIN::exec returns zero > + result already there, therefore execution reaches this point only if > + exec_const_cond is TRUE. Since it is equvalent to join->conds, then > + join->conds is also TRUE. > */ > - if (!join->conds || join->conds->val_int()) > + if (!join->conds || join->exec_const_cond || join->conds->val_int()) > { > error= (*end_select)(join, 0, 0); > if (error == NESTED_LOOP_OK || error == NESTED_LOOP_QUERY_LIMIT) > > _______________________________________________ > commits mailing list > [email protected] > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
