From 987e146604d741f13fa03b7cb5e4af1cf3e30324 Mon Sep 17 00:00:00 2001 From: Evgeny Potemkin Date: Wed, 2 Dec 2009 00:25:51 +0300 Subject: Bug#33546: Slowdown on re-evaluation of constant expressions. Constant expressions in WHERE/HAVING/ON clauses aren't cached and evaluated for each row. This causes slowdown of query execution especially if constant UDF/SP function are used. Now WHERE/HAVING/ON expressions are analyzed in the top-bottom direction with help of the compile function. When analyzer meets a constant item it sets a flag for the tree transformer to cache the item and doesn't allow tree walker to go deeper. Thus, the topmost item of a constant expression if cached. This is done after all other optimizations were applied to WHERE/HAVING/ON expressions A helper function called cache_const_exprs is added to the JOIN class. It calls compile method with caching analyzer and transformer on WHERE, HAVING, ON expressions if they're present. The cache_const_expr_analyzer and cache_const_expr_transformer functions are added to the Item class. The first one check if the item can be cached and the second caches it if so. A new Item_cache_datetime class is derived from the Item_cache class. It caches both int and string values of the underlying item independently to avoid DATETIME aware int-to-string conversion. Thus it completely relies on the ability of the underlying item to correctly convert DATETIME value from int to string and vice versa. mysql-test/r/func_like.result: A test case result is corrected after fixing bug#33546. mysql-test/r/func_time.result: A test case result is corrected after fixing bug#33546. mysql-test/r/select.result: Added a test case for the bug#33546. mysql-test/r/subselect.result: A test case result is corrected after fixing bug#33546. mysql-test/r/udf.result: Added a test case for the bug#33546. mysql-test/t/select.test: Added a test case for the bug#33546. mysql-test/t/udf.test: Added a test case for the bug#33546. sql/item.cc: Bug#33546: Slowdown on re-evaluation of constant expressions. The cache_const_expr_analyzer and cache_const_expr_transformer functions are added to the Item class. The first one check if the item can be cached and the second caches it if so. Item_cache_datetime class implementation is added. sql/item.h: Bug#33546: Slowdown on re-evaluation of constant expressions. Item_ref and Item_cache classes now returns basic_const_item from underlying item. The cache_const_expr_analyzer and cache_const_expr_transformer functions are added to the Item class. sql/sql_select.cc: Bug#33546: Slowdown on re-evaluation of constant expressions. A helper function called cache_const_exprs is added to the JOIN class. It calls compile method with caching analyzer and transformer on WHERE, HAVING, ON expressions if they're present. sql/sql_select.h: Bug#33546: Slowdown on re-evaluation of constant expressions. A helper function called cache_const_exprs is added to the JOIN class. --- mysql-test/t/udf.test | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/t/udf.test') diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 7bf252040e5..7e383ce69fa 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -449,3 +449,17 @@ SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 ); DROP TABLE t1; --echo End of 5.0 tests. + +--echo # +--echo # Bug#33546: Slowdown on re-evaluation of constant expressions. +--echo # +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES(1),(50); +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION myfunc_double RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE f1=1 + myfunc_double(1); +DROP FUNCTION myfunc_double; +DROP TABLE t1; +--echo # +--echo End of 5.1 tests. + -- cgit v1.2.1