Hi guys!
Sergey, would this be something that might be better as a server
variable, configurable at runtime? Seems like, given your patch below,
a community member might be able to put together a proper patch for
MySQL to have a configurable server setting to control this behaviour?
What do you think? Are there difficulties I should be aware of?
Cheers!
Jay
Mark Callaghan wrote:
> 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
>>
>>
>
>
>
