From: Mark Callaghan Date: July 19 2007 6:11pm Subject: Re: How can I disable equality propagation in MySQL 5.0 List-Archive: http://lists.mysql.com/internals/34862 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit That did it. Thank you for the prompt response. MySQL 5 performance looks very good now. On 7/18/07, Sergey Petrunia wrote: > On Tue, Jul 17, 2007 at 08:12:59PM -0700, Mark Callaghan wrote: > > MySQL 5.0 added equality propagation to the optimizer. This has made > > some of my queries much slower, both the cost of optimization and the > > resulting plans. The problem occurs for queries with large inlists. > > For example: > > > > select * from t1, t2 where t1.a = t2.a and t1.a in () > > and t2.b between Const1 and Const2 > > > > In this query, the optimizer appears to be adding: t2.a in > > () and this is done despite the size of the inlist. > Strictly speaking it is not added, it is the occurrence of 't1.a' in the IN > predicate becomes aware that 't2.a' can be used instead of it. Then all > optimizations take advantage of that. > > > Many of my queries have thousands of values in these inlists. I know > > that predicates are being added because handler::records_in_range is > > called on t2 by check_quick_keys() once for each value in the inlist. > > > > I want to disable equality propagation for large inlists, but I have > > yet to figure out how to do this. I have disabled it for tables for > > which there is a FORCE INDEX hint (done by adding code to > > check_simple_equality), but it is too much work to add hints to all > > queries with large inlists. I have added code to > > build_equal_items_for_cond() to disable calls to check_equality() for > > items that are large inlists, but that does not work. I need to either > > prevent propagation somewhere else in the code or do something in > > build_equal_items_for_cond() when the join condition is processed. > > > > Can someone point me to the right place in the code > > Here is a patch that will disable equality propagation into IN predicates > that have inlists that are big enough: > > ===== item_cmpfunc.h 1.153 vs edited ===== > --- 1.153/sql/item_cmpfunc.h 2007-07-19 03:26:18 +04:00 > +++ edited/item_cmpfunc.h 2007-07-19 02:31:15 +04:00 > @@ -1144,6 +1144,8 @@ class Item_func_in :public Item_func_opt_neg > bool nulls_in_row(); > bool is_bool_func() { return 1; } > CHARSET_INFO *compare_collation() { return cmp_collation.collation; } > + /* sergeyp: don't do eq substs inside IN if the IN-list has > 5 elements */ > + bool subst_argument_checker(byte **arg) { return test(arg_count < 5); } > }; > > class cmp_item_row :public cmp_item > ===== sql_select.cc 1.538 vs edited ===== > --- 1.538/sql/sql_select.cc 2007-07-19 03:26:18 +04:00 > +++ edited/sql_select.cc 2007-07-19 02:29:39 +04:00 > @@ -7375,11 +7375,15 @@ static COND *build_equal_items_for_cond( > an argument of a comparison predicate. > */ > byte *is_subst_valid= (byte *) 1; > - cond= cond->compile(&Item::subst_argument_checker, > + Item *new_cond; > + if ((new_cond= cond->compile(&Item::subst_argument_checker, > &is_subst_valid, > &Item::equal_fields_propagator, > - (byte *) inherited); > - cond->update_used_tables(); > + (byte *) inherited)) && (new_cond != cond)) > + { > + cond= new_cond; > + cond->update_used_tables(); > + } > } > return cond; > } > > BR > Sergey > -- > Sergey Petrunia, Software Developer > MySQL AB, www.mysql.com > Office: N/A > Blog: http://s.petrunia.net/blog > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=mcallaghan@stripped > > -- Mark Callaghan mcallaghan@stripped