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
