That did it. Thank you for the prompt response. MySQL 5 performance
looks very good now.
On 7/18/07, Sergey Petrunia <spetrunia@stripped> 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 (<large_in_list>)
> > and t2.b between Const1 and Const2
> >
> > In this query, the optimizer appears to be adding: t2.a in
> > (<large_in_list>) 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=1
>
>
--
Mark Callaghan
mcallaghan@stripped
