- developers - lists.mariadb.org

[Maria-developers] New (by Psergey): Push conditions down into non-mergeable VIEWs when possible (119)
by worklog-noreply＠askmonty.org 24 May '10

24 May '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Push conditions down into non-mergeable VIEWs when possible CREATION DATE..: Mon, 24 May 2010, 20:52 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 119 (http://askmonty.org/worklog/?tid=119) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: There are complaints (see links below) about cases with non-mergeable VIEW (because it has a GROUP BY), a query that has restrictions on the grouped column, and poor performance that is caused by VIEW processing code ignoring the restriction. This WL is about addressing this issue. links to complaints: http://code.openark.org/blog/mysql/views-better-performance-with-condition-… http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-perform… The target version is MariaDB 5.3+, because it has late optimization/execution for FROM-subqueries/non mergeable VIEWs, which makes it much more feasible to inject something into VIEW before it is optimized/executed. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Push conditions down into non-mergeable VIEWs when possible (119)
by worklog-noreply＠askmonty.org 24 May '10

24 May '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Push conditions down into non-mergeable VIEWs when possible CREATION DATE..: Mon, 24 May 2010, 20:52 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 119 (http://askmonty.org/worklog/?tid=119) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: There are complaints (see links below) about cases with non-mergeable VIEW (because it has a GROUP BY), a query that has restrictions on the grouped column, and poor performance that is caused by VIEW processing code ignoring the restriction. This WL is about addressing this issue. links to complaints: http://code.openark.org/blog/mysql/views-better-performance-with-condition-… http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-perform… The target version is MariaDB 5.3+, because it has late optimization/execution for FROM-subqueries/non mergeable VIEWs, which makes it much more feasible to inject something into VIEW before it is optimized/executed. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Rev 2789: Subquery cache (as is) in file:///home/bell/maria/bzr/work-maria-5.3-scache/
by sanja＠askmonty.org 24 May '10

24 May '10

At file:///home/bell/maria/bzr/work-maria-5.3-scache/ ------------------------------------------------------------ revno: 2789 revision-id: sanja(a)askmonty.org-20100524172956-7b14x01aodizr3sq parent: sergii(a)pisem.net-20100510134608-oyi2vznyghgcrt0x committer: sanja(a)askmonty.org branch nick: work-maria-5.3-scache timestamp: Mon 2010-05-24 20:29:56 +0300 message: Subquery cache (as is) === modified file 'libmysqld/Makefile.am' --- a/libmysqld/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/libmysqld/Makefile.am 2010-05-24 17:29:56 +0000 @@ -80,7 +80,8 @@ sql_tablespace.cc \ rpl_injector.cc my_user.c partition_info.cc \ sql_servers.cc event_parse_data.cc opt_table_elimination.cc \ - multi_range_read.cc opt_index_cond_pushdown.cc + multi_range_read.cc opt_index_cond_pushdown.cc \ + sql_subquery_cache.cc libmysqld_int_a_SOURCES= $(libmysqld_sources) nodist_libmysqld_int_a_SOURCES= $(libmysqlsources) $(sqlsources) === modified file 'sql/CMakeLists.txt' --- a/sql/CMakeLists.txt 2010-03-20 12:01:47 +0000 +++ b/sql/CMakeLists.txt 2010-05-24 17:29:56 +0000 @@ -78,7 +78,7 @@ rpl_rli.cc rpl_mi.cc sql_servers.cc sql_connect.cc scheduler.cc sql_profile.cc event_parse_data.cc opt_table_elimination.cc - ds_mrr.cc + ds_mrr.cc sql_subquery_cache.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.h ${PROJECT_SOURCE_DIR}/include/mysqld_error.h === modified file 'sql/Makefile.am' --- a/sql/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/sql/Makefile.am 2010-05-24 17:29:56 +0000 @@ -80,7 +80,7 @@ event_data_objects.h event_scheduler.h \ sql_partition.h partition_info.h partition_element.h \ contributors.h sql_servers.h \ - multi_range_read.h + multi_range_read.h sql_subquery_cache.h mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ @@ -130,7 +130,7 @@ sql_servers.cc event_parse_data.cc \ opt_table_elimination.cc \ multi_range_read.cc \ - opt_index_cond_pushdown.cc + opt_index_cond_pushdown.cc sql_subquery_cache.cc nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c my_time.c my_user.c === modified file 'sql/item.cc' --- a/sql/item.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item.cc 2010-05-24 17:29:56 +0000 @@ -2273,6 +2273,13 @@ str->append(str_value); } +void Item_bool_cache::print(String *str, enum_query_type query_type) +{ + if (null_value) + str->append("NULL", 4); + else + Item_int::print(str, query_type); +} Item_uint::Item_uint(const char *str_arg, uint length): Item_int(str_arg, length) @@ -3646,12 +3653,17 @@ resolved_item->db_name : ""); const char *table_name= (resolved_item->table_name ? resolved_item->table_name : ""); + DBUG_ENTER("mark_as_dependent"); + DBUG_PRINT("enter", ("Field '%s.%s.%s in select %d resolved in %d", + db_name, table_name, + resolved_item->field_name, current->select_number, + last->select_number)); /* store pointer on SELECT_LEX from which item is dependent */ if (mark_item) mark_item->depended_from= last; if (current->mark_as_dependent(thd, last, /** resolved_item psergey-thu **/mark_item)) - return TRUE; + DBUG_RETURN(TRUE); if (thd->lex->describe & DESCRIBE_EXTENDED) { push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, @@ -3661,7 +3673,7 @@ resolved_item->field_name, current->select_number, last->select_number); } - return FALSE; + DBUG_RETURN(FALSE); } @@ -3698,6 +3710,7 @@ resolving) */ SELECT_LEX *previous_select= current_sel; + for (; previous_select->outer_select() != last_select; previous_select= previous_select->outer_select()) { @@ -3726,6 +3739,7 @@ mark_as_dependent(thd, last_select, current_sel, resolved_item, dependent); } + return; } @@ -4098,6 +4112,9 @@ ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); return 0; } } @@ -4113,7 +4130,9 @@ ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); - + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* A reference to a view field had been found and we substituted it instead of this Item (find_field_in_tables @@ -4215,6 +4234,10 @@ mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, rf, rf); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); + return 0; } else @@ -4222,6 +4245,9 @@ mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, (Item_ident*)*reference); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); if (last_checked_context->select_lex->having_fix_field) { Item_ref *rf; @@ -5973,6 +5999,9 @@ refer_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* view reference found, we substituted it instead of this Item, so can quit @@ -6023,6 +6052,9 @@ thd->change_item_tree(reference, fld); mark_as_dependent(thd, last_checked_context->select_lex, thd->lex->current_select, fld, fld); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of @@ -6046,6 +6078,9 @@ DBUG_ASSERT(*ref && (*ref)->fixed); mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, this); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + ref); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of === modified file 'sql/item.h' --- a/sql/item.h 2010-03-20 12:01:47 +0000 +++ b/sql/item.h 2010-05-24 17:29:56 +0000 @@ -1143,6 +1143,11 @@ { return Field::GEOM_GEOMETRY; }; String *check_well_formed_result(String *str, bool send_error= 0); bool eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs); + + /** + Used to get reference on real item (not Item_ref) + */ + virtual Item **unref(Item **my_ref) { return my_ref; }; }; @@ -1922,8 +1927,31 @@ virtual void print(String *str, enum_query_type query_type); Item_num *neg (); uint decimal_precision() const { return max_length; } - bool check_partition_func_processor(uchar *bool_arg) { return FALSE;} - bool check_vcol_func_processor(uchar *arg) { return FALSE;} +}; + + +/** + Item represent TRUE/FALSE/NULL for subquery values +*/ + +class Item_bool_cache: public Item_int +{ +public: + Item_bool_cache(): Item_int(0, 1) + { + unsigned_flag= maybe_null= null_value= TRUE; + name= (char *)"bool chache"; + } + Item_bool_cache(my_bool val, my_bool null): Item_int(val, 1) + { + unsigned_flag= maybe_null= TRUE; + null_value= null; + name= (char *)"bool chache"; + } + Item *clone_item() { return new Item_bool_cache(value, null_value); } + uint decimal_precision() const { return 1; } + virtual void print(String *str, enum_query_type query_type); + void set(my_bool val, my_bool null) {value= test(val); null_value= null;} }; @@ -2479,6 +2507,11 @@ { return trace_unsupported_by_check_vcol_func_processor("ref"); } + + /** + Used to get reference on real item (not Item_ref) + */ + virtual Item **unref(Item **my_ref) {return (*ref)->unref(ref); }; }; @@ -3146,7 +3179,8 @@ example(0), used_table_map(0), cached_field(0), cached_field_type(MYSQL_TYPE_STRING), value_cached(0) { - fixed= 1; + fixed= 1; + maybe_null= 1; null_value= 1; } Item_cache(enum_field_types field_type_arg): @@ -3154,6 +3188,7 @@ value_cached(0) { fixed= 1; + maybe_null= 1; null_value= 1; } === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.cc 2010-05-24 17:29:56 +0000 @@ -1736,6 +1736,13 @@ used_tables_cache|= args[1]->used_tables(); not_null_tables_cache|= args[1]->not_null_tables(); const_item_cache&= args[1]->const_item(); + DBUG_ASSERT(scache == NULL); + if (args[0]->cols() ==1 && + thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE) + { + sub->depends_on.push_front((Item**)&cache); + scache= new Subquery_cache_tmptable(thd, sub->depends_on, &result); + } fixed= 1; return FALSE; } @@ -1744,10 +1751,26 @@ longlong Item_in_optimizer::val_int() { bool tmp; + DBUG_ENTER("Item_in_optimizer::val_int"); + DBUG_ASSERT(fixed == 1); cache->store(args[0]); cache->cache_value(); - + + /* check if result is in the cache */ + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + { + tmp= cached_value->val_int(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + } + if (cache->null_value) { /* @@ -1818,11 +1841,18 @@ for (uint i= 0; i < ncols; i++) item_subs->set_cond_guard_var(i, TRUE); } - return 0; + DBUG_RETURN(0); } tmp= args[1]->val_bool_result(); null_value= args[1]->null_value; - return tmp; + + /* put result in the cache */ + if (scache) + { + result.set(tmp, null_value); + scache->put_value(&result); + } + DBUG_RETURN(tmp); } @@ -1839,6 +1869,11 @@ Item_bool_func::cleanup(); if (!save_cache) cache= 0; + if (scache) + { + delete scache; + scache= 0; + } DBUG_VOID_RETURN; } === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.h 2010-05-24 17:29:56 +0000 @@ -215,6 +215,7 @@ class Item_cache; +class Subquery_cache; #define UNKNOWN ((my_bool)-1) @@ -237,6 +238,10 @@ { protected: Item_cache *cache; + /* Subquery cache */ + Subquery_cache *scache; + /* result representation for the subquery cache */ + Item_bool_cache result; bool save_cache; /* Stores the value of "NULL IN (SELECT ...)" for uncorrelated subqueries: @@ -247,7 +252,7 @@ my_bool result_for_null_param; public: Item_in_optimizer(Item *a, Item_in_subselect *b): - Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), + Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), scache(NULL), save_cache(0), result_for_null_param(UNKNOWN) {} bool fix_fields(THD *, Item **); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-03-29 14:04:35 +0000 +++ b/sql/item_subselect.cc 2010-05-24 17:29:56 +0000 @@ -1,4 +1,4 @@ -/* Copyright (C) 2000 MySQL AB +/* Copyrigh (C) 2000 MySQL AB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -34,11 +34,10 @@ Item_subselect::Item_subselect(): Item_result_field(), value_assigned(0), thd(0), substitution(0), - engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0), - const_item_cache(1), - inside_first_fix_fields(0), done_first_fix_fields(FALSE), - eliminated(FALSE), - engine_changed(0), changed(0), is_correlated(FALSE) + engine(0), old_engine(0), scache(0), used_tables_cache(0), + have_to_be_excluded(0), const_item_cache(1), inside_first_fix_fields(0), + done_first_fix_fields(FALSE), eliminated(FALSE), engine_changed(0), + changed(0), is_correlated(FALSE) { with_subselect= 1; reset(); @@ -116,6 +115,12 @@ } if (engine) engine->cleanup(); + depends_on.empty(); + if (scache) + { + delete scache; + scache= 0; + } reset(); value_assigned= 0; DBUG_VOID_RETURN; @@ -148,6 +153,8 @@ Item_subselect::~Item_subselect() { delete engine; + if (scache) + delete scache; } Item_subselect::trans_res @@ -746,9 +753,19 @@ void Item_singlerow_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_singlerow_subselect::fix_length_and_dec"); if ((max_columns= engine->cols()) == 1) { + DBUG_PRINT("info", ("one, elements: %u flag %u", + (uint)depends_on.elements, + (uint)test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE))); engine->fix_length_and_dec(row= &value); + if (depends_on.elements && optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE)) + { + DBUG_ASSERT(scache == NULL); + scache= new Subquery_cache_tmptable(thd, depends_on, value); + DBUG_PRINT("info", ("cache: 0x%lx", (ulong) scache)); + } } else { @@ -765,6 +782,7 @@ */ if (engine->no_tables()) maybe_null= engine->may_be_null(); + DBUG_VOID_RETURN; } uint Item_singlerow_subselect::cols() @@ -797,77 +815,200 @@ exec(); } + +Item *Item_subselect::check_cache() +{ + DBUG_ENTER("Item_subselect::check_cache"); + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + DBUG_RETURN(cached_value); + } + DBUG_RETURN(NULL); +} + double Item_singlerow_subselect::val_real() { + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_real"); DBUG_ASSERT(fixed == 1); - if (!exec() && !value->null_value) + + if ((cached_value = check_cache())) + { + double res= cached_value->val_real(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_real(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_real()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } longlong Item_singlerow_subselect::val_int() { + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_int"); DBUG_ASSERT(fixed == 1); - if (!exec() && !value->null_value) + + if ((cached_value = check_cache())) + { + longlong res= cached_value->val_int(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_int(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_int()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } String *Item_singlerow_subselect::val_str(String *str) { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_str"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + String *res= cached_value->val_str(str); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_str(str); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_str(str)); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_decimal"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_decimal *res= cached_value->val_decimal(decimal_value); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_decimal(decimal_value); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_decimal(decimal_value)); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } bool Item_singlerow_subselect::val_bool() { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_bool"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + bool res= cached_value->val_bool(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_bool(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_bool()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } @@ -952,33 +1093,77 @@ void Item_exists_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_exists_subselect::fix_length_and_dec"); decimals= 0; max_length= 1; max_columns= engine->cols(); /* We need only 1 row to determine existence */ unit->global_parameters->select_limit= new Item_int((int32) 1); + if (substype() == EXISTS_SUBS && depends_on.elements && + optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE)) + { + DBUG_ASSERT(scache == NULL); + scache= new Subquery_cache_tmptable(thd, depends_on, &result); + DBUG_PRINT("info", ("cache: 0x%lx", (ulong) scache)); + } + DBUG_VOID_RETURN; } double Item_exists_subselect::val_real() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_int"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + double res= cached_value->val_real(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) { reset(); - return 0; - } - return (double) value; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN((double) value); } longlong Item_exists_subselect::val_int() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_real"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + longlong res= cached_value->val_int(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + DBUG_ASSERT(fixed == 1); if (exec()) { reset(); - return 0; - } - return value; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN(value); } @@ -997,11 +1182,31 @@ String *Item_exists_subselect::val_str(String *str) { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_str"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + String *res= cached_value->val_str(str); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) + { reset(); + DBUG_RETURN(NULL); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + str->set((ulonglong)value,&my_charset_bin); - return str; + DBUG_RETURN(str); } @@ -1020,23 +1225,60 @@ my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value) { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_decvimal"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_decimal *res= cached_value->val_decimal(decimal_value); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) + { reset(); + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value); - return decimal_value; + DBUG_RETURN(decimal_value); } bool Item_exists_subselect::val_bool() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_real"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_bool res= cached_value->val_bool(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) { reset(); - return 0; - } - return value != 0; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN(value != 0); } === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-03-29 14:04:35 +0000 +++ b/sql/item_subselect.h 2010-05-24 17:29:56 +0000 @@ -27,6 +27,7 @@ class subselect_hash_sj_engine; class Item_bool_func2; class Cached_item; +class Subquery_cache; /* base class for subselects */ @@ -57,6 +58,10 @@ subselect_engine *engine; /* old engine if engine was changed */ subselect_engine *old_engine; + /* subquery cache */ + Subquery_cache *scache; + /* null consrtant for caching */ + Item_null const_null_value; /* cache of used external tables */ table_map used_tables_cache; /* allowed number of columns (1 for single value subqueries) */ @@ -67,7 +72,7 @@ bool have_to_be_excluded; /* cache of constant state */ bool const_item_cache; - + bool inside_first_fix_fields; bool done_first_fix_fields; public: @@ -88,13 +93,18 @@ */ List<Ref_to_outside> upper_refs; st_select_lex *parent_select; - - /* + + /** + List of items subquery depends on (externally resolved); + */ + List<Item*> depends_on; + + /* TRUE<=>Table Elimination has made it redundant to evaluate this select (and so it is not part of QEP, etc) - */ + */ bool eliminated; - + /* changed engine indicator */ bool engine_changed; /* subquery is transformed */ @@ -178,6 +188,8 @@ return trace_unsupported_by_check_vcol_func_processor("subselect"); } + Item *check_cache(); + /** Get the SELECT_LEX structure associated with this Item. @return the SELECT_LEX structure associated with this Item @@ -202,6 +214,7 @@ { protected: Item_cache *value, **row; + public: Item_singlerow_subselect(st_select_lex *select_lex); Item_singlerow_subselect() :Item_subselect(), value(0), row (0) {} @@ -268,6 +281,8 @@ { protected: bool value; /* value of this item (boolean: exists/not-exists) */ + /* result representation for the subquery cache */ + Item_bool_cache result; public: Item_exists_subselect(st_select_lex *select_lex); === modified file 'sql/mysql_priv.h' --- a/sql/mysql_priv.h 2010-03-20 12:01:47 +0000 +++ b/sql/mysql_priv.h 2010-05-24 17:29:56 +0000 @@ -568,12 +568,13 @@ #define OPTIMIZER_SWITCH_SEMIJOIN 256 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE 512 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN 1024 +#define OPTIMIZER_SWITCH_SUBQUERY_CACHE (1<<11) #ifdef DBUG_OFF -# define OPTIMIZER_SWITCH_LAST 2048 +# define OPTIMIZER_SWITCH_LAST (1<<12) #else -# define OPTIMIZER_SWITCH_TABLE_ELIMINATION 2048 -# define OPTIMIZER_SWITCH_LAST 4096 +# define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<12) +# define OPTIMIZER_SWITCH_LAST (1<<13) #endif #ifdef DBUG_OFF @@ -588,7 +589,8 @@ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #else # define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -601,7 +603,8 @@ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #endif /* @@ -936,6 +939,7 @@ #ifdef MYSQL_SERVER #include "sql_servers.h" #include "opt_range.h" +#include "sql_subquery_cache.h" #ifdef HAVE_QUERY_CACHE struct Query_cache_query_flags @@ -1269,6 +1273,10 @@ Item *having, ORDER *proc_param, ulonglong select_type, select_result *result, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex); + +struct st_join_table *create_index_lookup_join_tab(TABLE *table); +int join_read_key2(THD *thd, struct st_join_table *tab, TABLE *table, + struct st_table_ref *table_ref); void free_underlaid_joins(THD *thd, SELECT_LEX *select); bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result); @@ -1288,6 +1296,7 @@ bool table_cant_handle_bit_fields, bool make_copy_field, uint convert_blob_length); +bool open_tmp_table(TABLE *table); void sp_prepare_create_field(THD *thd, Create_field *sql_field); int prepare_create_field(Create_field *sql_field, uint *blob_columns, === modified file 'sql/mysqld.cc' --- a/sql/mysqld.cc 2010-03-20 12:01:47 +0000 +++ b/sql/mysqld.cc 2010-05-24 17:29:56 +0000 @@ -305,6 +305,7 @@ "firstmatch","loosescan","materialization", "semijoin", "partial_match_rowid_merge", "partial_match_table_scan", + "subquery_cache", #ifndef DBUG_OFF "table_elimination", #endif @@ -325,6 +326,7 @@ sizeof("semijoin") - 1, sizeof("partial_match_rowid_merge") - 1, sizeof("partial_match_table_scan") - 1, + sizeof("subquery_cache") - 1, #ifndef DBUG_OFF sizeof("table_elimination") - 1, #endif @@ -404,8 +406,9 @@ static const char *optimizer_switch_str="index_merge=on,index_merge_union=on," "index_merge_sort_union=on," "index_merge_intersection=on," - "index_condition_pushdown=on" -#ifndef DBUG_OFF + "index_condition_pushdown=on," + "subquery_cache=on" +#ifndef DBUG_OFF ",table_elimination=on"; #else ; @@ -5872,7 +5875,9 @@ OPT_RECORD_RND_BUFFER, OPT_DIV_PRECINCREMENT, OPT_RELAY_LOG_SPACE_LIMIT, OPT_RELAY_LOG_PURGE, OPT_SLAVE_NET_TIMEOUT, OPT_SLAVE_COMPRESSED_PROTOCOL, OPT_SLOW_LAUNCH_TIME, - OPT_SLAVE_TRANS_RETRIES, OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, + OPT_SLAVE_TRANS_RETRIES, + OPT_SUBQUERY_CACHE, + OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, OPT_DEBUGGING, OPT_DEBUG_FLUSH, OPT_SORT_BUFFER, OPT_TABLE_OPEN_CACHE, OPT_TABLE_DEF_CACHE, OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE, @@ -7164,7 +7169,7 @@ {"optimizer_switch", OPT_OPTIMIZER_SWITCH, "optimizer_switch=option=val[,option=val...], where option={index_merge, " "index_merge_union, index_merge_sort_union, index_merge_intersection, " - "index_condition_pushdown" + "index_condition_pushdown, subquery_cache" #ifndef DBUG_OFF ", table_elimination" #endif @@ -7868,6 +7873,8 @@ {"Ssl_version", (char*) &show_ssl_get_version, SHOW_FUNC}, #endif /* HAVE_OPENSSL */ {"Syncs", (char*) &my_sync_count, SHOW_LONG_NOFLUSH}, + {"Subquery_cache_hit", (char*) &subquery_cache_hit, SHOW_LONG}, + {"Subquery_cache_miss", (char*) &subquery_cache_miss, SHOW_LONG}, {"Table_locks_immediate", (char*) &locks_immediate, SHOW_LONG}, {"Table_locks_waited", (char*) &locks_waited, SHOW_LONG}, #ifdef HAVE_MMAP @@ -8006,6 +8013,7 @@ abort_loop= select_thread_in_use= signal_thread_in_use= 0; ready_to_exit= shutdown_in_progress= grant_option= 0; aborted_threads= aborted_connects= 0; + subquery_cache_miss= subquery_cache_hit= 0; delayed_insert_threads= delayed_insert_writes= delayed_rows_in_use= 0; delayed_insert_errors= thread_created= 0; specialflag= 0; === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_base.cc 2010-05-24 17:29:56 +0000 @@ -8062,6 +8062,10 @@ if (*conds) { thd->where="where clause"; + DBUG_EXECUTE("where", + print_where(*conds, + "WHERE in setup_conds", + QT_ORDINARY);); if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) || (*conds)->check_cols(1)) goto err_no_arena; === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_class.cc 2010-05-24 17:29:56 +0000 @@ -3020,6 +3020,7 @@ table_charset= 0; precomputed_group_by= 0; bit_fields_as_long= 0; + skip_create_table= 0; DBUG_VOID_RETURN; } === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-03-20 12:01:47 +0000 +++ b/sql/sql_class.h 2010-05-24 17:29:56 +0000 @@ -2786,12 +2786,17 @@ that MEMORY tables cannot index BIT columns. */ bool bit_fields_as_long; + /* + Whether to create or postpone actual creation of this temporary table. + TRUE <=> create_tmp_table will create only the TABLE structure. + */ + bool skip_create_table; TMP_TABLE_PARAM() :copy_field(0), group_parts(0), group_length(0), group_null_parts(0), convert_blob_length(0), schema_table(0), precomputed_group_by(0), force_copy_fields(0), - bit_fields_as_long(0) + bit_fields_as_long(0), skip_create_table(0) {} ~TMP_TABLE_PARAM() { === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_lex.cc 2010-05-24 17:29:56 +0000 @@ -1829,6 +1829,53 @@ } +/** + Registers reference on items on which the subqueries depends + + @param last pointer to last st_select_lex struct, before + which all st_select_lex have to be marked as + dependent + @param dependency reference on the item on which all this + subqueries depends + +*/ + +void st_select_lex::register_dependency_item(st_select_lex *last, + Item **dependency) +{ + SELECT_LEX *s= this; + DBUG_ENTER("st_select_lex::register_dependency_item"); + DBUG_ASSERT(this != last); + DBUG_ASSERT(*dependency); + dependency= (*dependency)->unref(dependency); + do + { + /* check duplicates */ + List_iterator_fast<Item*> li(s->master_unit()->item->depends_on); + Item **dep; + while ((dep= li++)) + { + if ((*dep)->eq(*dependency, FALSE)) + { + DBUG_PRINT("info", ("dependency %s already present", + ((*dependency)->name ? + (*dependency)->name : + "<no name>"))); + DBUG_VOID_RETURN; + } + } + + s->master_unit()->item->depends_on.push_back(dependency); + DBUG_PRINT("info", ("depends_on: Select: %d added: %s", + s->select_number, + ((*dependency)->name ? + (*dependency)->name : + "<no name>"))); + } while ((s= s->outer_select()) != last && s != 0); + DBUG_VOID_RETURN; +} + + /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from this to 'last' as dependent @@ -1843,7 +1890,7 @@ bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency) { - + DBUG_ENTER("st_select_lex::mark_as_dependent"); DBUG_ASSERT(this != last); /* @@ -1872,11 +1919,11 @@ Item_subselect *subquery_expr= s->master_unit()->item; if (subquery_expr && subquery_expr->mark_as_dependent(thd, last, dependency)) - return TRUE; + DBUG_RETURN(TRUE); } while ((s= s->outer_select()) != last && s != 0); is_correlated= TRUE; this->master_unit()->item->is_correlated= TRUE; - return FALSE; + DBUG_RETURN(FALSE); } bool st_select_lex_node::set_braces(bool value) { return 1; } === modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2010-03-20 12:01:47 +0000 +++ b/sql/sql_lex.h 2010-05-24 17:29:56 +0000 @@ -748,6 +748,7 @@ } bool mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency); + void register_dependency_item(st_select_lex *last, Item **dependency); bool set_braces(bool value); bool inc_in_sum_expr(); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-05-10 13:46:08 +0000 +++ b/sql/sql_select.cc 2010-05-24 17:29:56 +0000 @@ -151,7 +151,6 @@ static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); -static int join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref); static void join_read_key_unlock_row(st_join_table *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); @@ -5209,7 +5208,7 @@ 'join->best_positions' contains a complete optimal extension of the current partial QEP. */ - DBUG_EXECUTE("opt", print_plan(join, join->tables, + DBUG_EXECUTE("opt", print_plan(join, n_tables, record_count, read_time, read_time, "optimal");); DBUG_RETURN(FALSE); @@ -7625,6 +7624,40 @@ /** + Creates and fills JOIN_TAB for index look up in temporary table + + @param table The table where to look up + + @return JOIN_TAB object or NULL in case of error +*/ + +JOIN_TAB *create_index_lookup_join_tab(TABLE *table) +{ + JOIN_TAB *tab; + DBUG_ENTER("create_index_lookup_join_tab"); + + if (!((tab= new JOIN_TAB))) + DBUG_RETURN(NULL); + tab->read_record.table= table; + tab->read_record.file=table->file; + /*tab->read_record.unlock_row= rr_unlock_row;*/ + tab->next_select=0; + tab->sorted= 1; + + table->status= STATUS_NO_RECORD; + tab->read_first_record= join_read_key; + /*tab->read_record.unlock_row= join_read_key_unlock_row;*/ + tab->read_record.read_record= join_no_more_records; + if (table->covering_keys.is_set(tab->ref.key) && + !table->no_keyread) + { + table->key_read=1; + table->file->extra(HA_EXTRA_KEYREAD); + } + DBUG_RETURN(tab); +} + +/** Give error if we some tables are done with a full join. This is used by multi_table_update and multi_table_delete when running @@ -10778,6 +10811,7 @@ case Item::REF_ITEM: case Item::NULL_ITEM: case Item::VARBIN_ITEM: + case Item::CACHE_ITEM: if (make_copy_field) { DBUG_ASSERT(((Item_result_field*)item)->result_field); @@ -11552,7 +11586,8 @@ &param->recinfo, select_options)) goto err; } - if (open_tmp_table(table)) + DBUG_PRINT("info", ("skip_create_table: %d", (int)param->skip_create_table)); + if (!param->skip_create_table && open_tmp_table(table)) goto err; thd->mem_root= mem_root_save; @@ -11700,16 +11735,17 @@ bool open_tmp_table(TABLE *table) { int error; + DBUG_ENTER("open_tmp_table"); if ((error= table->file->ha_open(table, table->s->table_name.str, O_RDWR, HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE))) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ table->db_stat=0; - return(1); + DBUG_RETURN(1); } (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ - return(0); + DBUG_RETURN(0); } @@ -12540,7 +12576,8 @@ else { /* Do index lookup in the materialized table */ - if ((res= join_read_key2(join_tab, sjm->table, sjm->tab_ref)) == 1) + if ((res= join_read_key2(join_tab->join->thd, join_tab, + sjm->table, sjm->tab_ref)) == 1) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if (res || !sjm->in_equality->val_int()) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); @@ -13323,61 +13360,61 @@ static int join_read_key(JOIN_TAB *tab) { - return join_read_key2(tab, tab->table, &tab->ref); + return join_read_key2(tab->join->thd, tab, tab->table, &tab->ref); } -/* +/* eq_ref access handler but generalized a bit to support TABLE and TABLE_REF not from the join_tab. See join_read_key for detailed synopsis. */ -static int -join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) +int join_read_key2(THD *thd, JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) { int error; + DBUG_ENTER("join_read_key2"); if (!table->file->inited) { table->file->ha_index_init(table_ref->key, tab->sorted); } /* TODO: Why don't we do "Late NULLs Filtering" here? */ - if (cmp_buffer_with_ref(tab->join->thd, table, table_ref) || + if (cmp_buffer_with_ref(thd, table, table_ref) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) { if (table_ref->key_err) { table->status=STATUS_NOT_FOUND; - return -1; + DBUG_RETURN(-1); } /* Moving away from the current record. Unlock the row in the handler if it did not match the partial WHERE. */ - if (tab->ref.has_record && tab->ref.use_count == 0) + if (table_ref->has_record && table_ref->use_count == 0) { tab->read_record.file->unlock_row(); - tab->ref.has_record= FALSE; + table_ref->has_record= FALSE; } error=table->file->ha_index_read_map(table->record[0], table_ref->key_buff, make_prev_keypart_map(table_ref->key_parts), HA_READ_KEY_EXACT); if (error && error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE) - return report_error(table, error); + DBUG_RETURN(report_error(table, error)); if (! error) { - tab->ref.has_record= TRUE; - tab->ref.use_count= 1; + table_ref->has_record= TRUE; + table_ref->use_count= 1; } } else if (table->status == 0) { - DBUG_ASSERT(tab->ref.has_record); - tab->ref.use_count++; + DBUG_ASSERT(table_ref->has_record); + table_ref->use_count++; } table->null_row=0; - return table->status ? -1 : 0; + DBUG_RETURN(table->status ? -1 : 0); } === modified file 'sql/table.cc' --- a/sql/table.cc 2010-03-20 12:01:47 +0000 +++ b/sql/table.cc 2010-05-24 17:29:56 +0000 @@ -20,6 +20,7 @@ #include "sql_trigger.h" #include <m_ctype.h> #include "my_md5.h" +#include "my_bit.h" /* INFORMATION_SCHEMA name */ LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")}; @@ -5096,6 +5097,115 @@ file->column_bitmaps_signal(); } + +/** + @brief + Allocate space for keys + + @param key_count number of keys to allocate. + + @details + Allocate space enough to fit 'key_count' keys for this table. + + @return FALSE space was successfully allocated. + @return TRUE an error occur. +*/ + +bool TABLE::alloc_keys(uint key_count) +{ + DBUG_ASSERT(!s->keys); + key_info= s->key_info= (KEY*) my_malloc(sizeof(KEY)*key_count, MYF(0)); + max_keys= key_count; + return !(key_info); +} + + +/** + @brief Adds one key to a temporary table. + + @param key_parts bitmap of fields that take a part in the key. + @param key_name name of the key + + @details + Creates a key for this table from fields which corresponds the bits set to 1 + in the 'key_parts' bitmap. The 'key_name' name is given to the newly created + key. + + @return <0 an error occur. + @return >=0 number of newly added key. +*/ + +int TABLE::add_tmp_key(ulonglong key_parts, const char *key_name) +{ + DBUG_ASSERT(s->keys< max_keys); + + KEY* keyinfo; + Field **reg_field; + uint i; + bool key_start= TRUE; + uint key_part_count= my_count_bits(key_parts); + KEY_PART_INFO* key_part_info= + (KEY_PART_INFO*) my_malloc(sizeof(KEY_PART_INFO)* key_part_count, MYF(0)); + if (!key_part_info) + return -1; + keyinfo= key_info + s->keys; + keyinfo->key_part=key_part_info; + keyinfo->usable_key_parts=keyinfo->key_parts= key_part_count; + keyinfo->key_length=0; + keyinfo->algorithm= HA_KEY_ALG_UNDEF; + keyinfo->name= (char *)key_name; + keyinfo->flags= HA_GENERATED_KEY; + keyinfo->rec_per_key= (ulong*)my_malloc(sizeof(ulong)*key_part_count, MYF(0)); + if (!keyinfo->rec_per_key) + return -1; + bzero(keyinfo->rec_per_key, sizeof(ulong)*key_part_count); + for (i= 0, reg_field=field ; + *reg_field; + i++, reg_field++) + { + if (!(key_parts & (1 << i))) + continue; + if (key_start) + (*reg_field)->key_start.set_bit(s->keys); + key_start= FALSE; + (*reg_field)->part_of_key.set_bit(s->keys); + (*reg_field)->flags|= PART_KEY_FLAG; + key_part_info->null_bit= (*reg_field)->null_bit; + key_part_info->null_offset= (uint) ((*reg_field)->null_ptr - + (uchar*) record[0]); + key_part_info->field= *reg_field; + key_part_info->offset= (*reg_field)->offset(record[0]); + key_part_info->length= (uint16) (*reg_field)->pack_length(); + keyinfo->key_length+= key_part_info->length; + /* TODO: + The below method of computing the key format length of the + key part is a copy/paste from opt_range.cc, and table.cc. + This should be factored out, e.g. as a method of Field. + In addition it is not clear if any of the Field::*_length + methods is supposed to compute the same length. If so, it + might be reused. + */ + key_part_info->store_length= key_part_info->length; + + if ((*reg_field)->real_maybe_null()) + key_part_info->store_length+= HA_KEY_NULL_LENGTH; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB || + (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR) + key_part_info->store_length+= HA_KEY_BLOB_LENGTH; + + key_part_info->type= (uint8) (*reg_field)->key_type(); + key_part_info->key_type = + ((ha_base_keytype) key_part_info->type == HA_KEYTYPE_TEXT || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ? + 0 : FIELDFLAG_BINARY; + key_part_info++; + } + set_if_bigger(s->max_key_length, keyinfo->key_length); + return ++s->keys - 1; +} + + /** @brief Check if this is part of a MERGE table with attached children. === modified file 'sql/table.h' --- a/sql/table.h 2010-03-20 12:01:47 +0000 +++ b/sql/table.h 2010-05-24 17:29:56 +0000 @@ -781,6 +781,7 @@ uint temp_pool_slot; /* Used by intern temp tables */ uint status; /* What's in record[0] */ uint db_stat; /* mode of file as in handler.h */ + uint max_keys; /* Size of allocated key_info array. */ /* number of select if it is derived table */ uint derived_select_number; int current_lock; /* Type of lock on table */ @@ -914,6 +915,8 @@ inline bool needs_reopen_or_name_lock() { return s->version != refresh_version; } bool is_children_attached(void); + bool alloc_keys(uint key_count); + int add_tmp_key(ulonglong key_parts, const char *key_name); }; enum enum_schema_table_state === modified file 'storage/maria/ha_maria.cc' --- a/storage/maria/ha_maria.cc 2010-03-20 12:01:47 +0000 +++ b/storage/maria/ha_maria.cc 2010-05-24 17:29:56 +0000 @@ -995,6 +995,8 @@ { MARIA_HA *tmp= file; file= 0; + if (!tmp) + return 0; return maria_close(tmp); }

1 0

[Maria-developers] internals of page row format
by Igor Kozyrev 19 May '10

19 May '10

Could anyone please explain data representation, both on disk and in memory? Especially about BLOB fields What was the reasons to move hot data from OS file cache to MySQL memory? Is it great improvement of efficiency(to avoid kernel calls) or necessity for future transaction support? -- This message was sent from Launchpad by the user Igor Kozyrev (https://launchpad.net/~ikozyrev36) using the "Contact this team" link on the Maria developers team page to each member directly. For more information see https://help.launchpad.net/YourAccount/ContactingPeople

1 0

[Maria-developers] Segmented Key Cache - About section
by Daniel Bartholomew 19 May '10

19 May '10

Hello everyone, I'm working on the documentation for the Segmented Key Cache, one of the new features in MariaDB 5.2. The documentation page will have "about", "syntax", and "example" sections. I've based the "About" section on the High-Level Description from the worklog ( http://askmonty.org/worklog/Server-Sprint/?tid=85 ) and from the description in the mysys/mf_keycache.c file in the source. Here is my first draft. I've changed the wording a bit to help it flow better and I've changed "partition" to "segment" in keeping with the official name. ---------------------------------------------------------------------- == About Segmented Key Cache == A segmented key cache is a collection of structures for regular MyISAM key caches called key cache segments. Segmented key caches mitigate one of the major problem of the simple key cache: thread contention for key cache lock (mutex). With regular key caches, every call of a key cache interface function must acquire this lock. Threads compete for this lock even when the file and pages they have acquired shared locks for are in the key cache buffers. When working with a segmented key cache any key cache interface function that needs only one page has to acquire the key cache lock only for the segment the page is assigned to. This makes the chances for threads not having to compete for the same key cache lock better. Any page from a file can be placed into a buffer of only one segment. The number of the segment is calculated from the file number and the position of the page in the file, and it's always the same for the page. Pages are evenly distributed among segments. The idea and the original code of the segmented key cache was provided by Fredrik Nylander from Stardoll.com. The code was extensively reworked, improved, and eventually merged into MariaDB by Igor Babaev from Monty Program. ---------------------------------------------------------------------- If there are any factual errors with the above, let me know. Improvements and suggestions are also welcome. One thing I'm not happy with is this sentence: "Threads compete for this lock even when the file and pages they have acquired shared locks for are in the key cache buffers." The original sentence from the worklog reads: So threads compete for this lock even in the case when they have acquired shared locks for the file and pages they want read from are in the key cache buffers. ... which is very confusing to me. If anyone has a better or more accurate rewording, please send it my way. Also, there's a note in the source code (lines 5018-5023 of mysys/mf_keycache.c) which states: Unfortunately if we use a partitioned key cache with N partitions for B-tree indexes we can't say that the chances becomes N times less. The fact is that any index lookup operation requires reading from the root page that, for any index, is always ascribed to the same partition. To resolve this problem we should have employed more sophisticated mechanisms of working with root pages. Do any of you have any opinions on whether or not this should be mentioned somewhere in the documentation? The only issue I have with mentioning it is that if I do, the first question that comes to my mind is: "If it is not N-times less, how much less is it?" I don't have an answer to that question (and math isn't one of my strengths). Next up will be the "Syntax" section. Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

3 6

Re: [Maria-developers] MySQL 5.1.46 diff of sql/sql_select.cc for review
by Kristian Nielsen 19 May '10

19 May '10

Igor Babaev <igor(a)askmonty.org> writes: > Here's my review. Thanks a lot for your help! I checked through all your points (few detailed comments below), all your proposed solutions seem reasonable to me. In summary, we should as you suggest: - Rework patches for bugs 39022, 48483, and 49324 as you described (1,7,8). - Revert bug 45640 patch (5) and instead apply your proposed patch. - Revert patches for bugs 51242 and 52336, and instead apply your proposed fix (15,18). - Reject (eg. revert in 5.1-release) patch for bug 39653 (2) - Do nothing for bugs 40277, 45195, 45989, 49902, 50995, and 51494 (3,4,6,10,14,16), as necessary changes were already pushed to 5.1-release. - Do nothing for bugs 49829, 50335, 50591, 50843, and 52177 (9,11,12,13,17), as the patches for those are ok. You proposed on the call yesterday that you could prepare a patch with these changes for our merge tree: lp:~maria-captains/maria/5.1-release Please do so. I will try to get hold of Monty and discuss with him, but I think he will agree with making your proposed changes. If you need a full review for your changes, you will probably need to ask someone else than me, as I am unfamiliar with this part of the code. Thanks, - Kristian. > 1. Bug #39022 (by Georgi Kodinov) > ---------------------------------- > > SYNOPSIS > Any call of SQL_SELECT::skip record ignore the fact that > during evaluation of an expression (item) an error may occur. > > CONCLUSION > The patch cannot be applied as it is, requires some rework. > > REASONS: > The patch is incomplete: > There are 5 calls of SQL_SELECT::skip_record in total: > two - in sql_select.cc, and three others in > filesort.cc, sql_delete.cc, sql_update.cc. > Only the calls in sql_select.cc are handled in an error aware > manner. The second call in sql_select is handled incorrectly: > the error occurred at the evaluation of the select condition > is caught only if the function returns false. > > POSSIBLE SOLUTION: > change the synopsis of SQL_SELECT::skip_record: > int SQL_SELECT::skip_record() > { > int rc= test(cond && !cond->val_int()); > if thd->is_error() rc=-1; > return rc; > } > > (thd must be added to SQL_SELECT) > > then after each call SQL_SELECT::retain_record > add error handling for the cases when > SQL_SELECT::skip_record returns -1: > int rc= 0; > if (!select || (rc= select->skip_record()) != 0) > { > if (rc < 0) > { > /* handle error returned by skip_record() */ > ... > } > ... > } Agree with your proposed solution. > 2. Bug #39653 (by Gleb Shchepa) > > SYNOPSIS > InnoDB covering primary index is used when using another > covering index is more beneficial. > > CONCLUSION > The patch must be rejected. > > REASONS: > The patch is based on completely wrong idea that any > covering secondary index is better than primary covering index > for scanning in InnoDB. > > Here an example demonstrating that it's not so: > CREATE TABLE t1 ( > a int, b int, c int, > PRIMARY KEY (a, b), > KEY idx (a,c) > ); > Both primary key and the secondary key here are covering for the query > SELECT a FROM t1 WHERE a BETWEEN 1000 and 5000; > Apparently scanning by the primary key will be faster here as it does > not use random seeks. > > The patch completely ignores non-InnoDB engines. > > POSSIBLE SOLUTION: > Cost based choice that takes into account that sequential access is > C times faster then random access. I am ok with rejecting the patch. It's hard to tell whether primary key or index will be better to use, in some cases secondary index may require no random seeks if it is not fragmented while primary key could be. In any case this seems a dangerous change in a stable release (user can force index of choice if he/she has more information about which will be best). > 3. Bug #40277 (by Davi Arnaut) > ------------------------------- > see the notes from Monty's review Ok, Monty already fixed this in 5.1-release. Agree. > 4. 45195 (by Sergey Glukhov) > ----------------------------- > SYNOPSIS > Reading uninitialized bytes from join cache buffer. > (Valgrind's complain) > > CONCLUSION > The patch should be accepted as it is > (Monty has some a comment on this patch though). Agree, Monty already pushed his fixes to 5.1-release. > 5. Bug 45640 (by Gleb Shchepa) > ------------------------------- > SYNOPSYS. > Building Item_ref objects of a wrong type for outer references used > in aliased expressions in a select with group by causes wrong > results. Group by expressions containing references to aliases may > cause wrong results. > > CONCLUSION. > The patch cannot be accepted as it is, requires a serious re-work. > > REASON. > Although the basic ideas behind the fix appear to be be valid their > implementation is quite clumsy: > -an unnecessary parameter is added to the function fix_inner_refs > -the info about the syntax context of a field referenced is passed > into Item_field::fix_fields in an unconventional and ugly manner > -the group expression are traversed for each reference of the list > of inner references > > POSSIBLE SOLUTION > See the patch at the very end of the post. Agree with proposed solution, using the patch at end. > 6. Bug #45989 (by Georgi Kodinov) > ---------------------------------- > This bug has been already fixed in MariaDB 5.1.44. > Our fix is correct, the fix by Georgi is not quite correct > (but not harmful). Agree, yes the better fix is already pushed. > 7. Bug #48483 (by Sergey Glukhov) > ---------------------------------- > (No public access) > > SYNOPSIS > Wrong calculation of table dependencies for join queries with > outer join operation causes a crash. > > CONCLUSION > The patch can be accepted with one change that matters > - if (!((prev_table->on_expr->used_tables() & ~RAND_TABLE_BIT) & > - ~prev_used_tables)) > + if (!((prev_table->on_expr->used_tables() & > + ~(OUTER_REF_BIT | RAND_TABLE_BIT)) & > + ~prev_used_tables)) I assume you mean OUTER_REF_TABLE_BIT here. Ok. > 8. Bug #49324 (by Georgi Kodinov) > ---------------------------------- > SYNOPSIS > With InnoDB a GROUP BY / ORDER BY can use an index extended by some > number of major components of the primary. The value of rec_per_key > for such extended indexes must be calculated in a special way. > > CONCLUSION > The patch could be accepted after changing the formula that calculates > the value of rec_per_key for an extended index: > > - rec_per_key= used_key_parts && > - used_key_parts <= keyinfo->key_parts ? > - keyinfo->rec_per_key[used_key_parts-1] : 1; > + int used_index_parts= keyinfo->key_parts; > + int used_pk_parts= 0; > + set_if_bigger(used_pk_parts, > + used_key_parts-used_index_parts); > + rec_per_key= keyinfo->rec_per_key[used_key_parts-1]; > + if (used_pk_parts) > + { > + KEY *pkinfo= tab->table->key_info+table->s->primary_key; > + rec_per_key*= pkinfo->rec_per_key[used_pk_parts-1]; > + rec_per_key/= pkinfo->rec_per_key[0]; > + } > > REASONS > The formula in the patch does not take into account how many > components of the of the primary key is used in the extended index. Ok (I don't understand the calculation, but not knowing the code I'm willing to take your word for it). > 9. Bug #49829 (by Staale Smedseng) > ---------------------------------- > SYNOPSYS > Compiler problems (warnings) for a platform > > CONCLUSION > THe patch is ok. Agree (it was kind of nice to see the explanation for these warnings, which I think I saw before but didn't know what meant). > 10. Bug #49902 (by Sergey Vojtovich) > ----------------------------------- > See the comments/suggestions from Monty's review Yes, this is pushed to 5.1-release (and I agree with Monty's comment). > 11. Bug #50335 (by Alexey Kopytov) > ----------------------------------- > (No public access) > > SYNOPSYS > Failure of a wrong assertion > > CONCLUSION > The patch is ok. Ok. > 12. Bug #50591 (by Sergey Glukhov) > ----------------------------------- > SYNOPSIS > Wrong result for a grouping query over a table with a BIT field > > CONCLUSION > The patch is ok. Ok. > 13. Bug #50843 (by Evgeny Potemkin) > ------------------------------------ > SYNOPSIS > Performance degradation problem when join cache + filesort > are used intead of a full index scan by a primary key. > > CONCLUSION > The patch looks ok. Ok. > 14. Bug #50995 (by Sergey Glukhov) > ------------------------------------ > SYNOPSIS > A badly formed list for conditions causes wrong query results. > > CONCLUSION > The patch looks ok for me. > See also Monty's recommendation from his review. Yes. As far as I can see, Monty pushed his changes from his review to 5.1-release. He did however not update the comments for eliminate_item_equal() as per his suggestion (maybe he forgot): "Note that we should update the function comment for eliminate_item_equal() as this can't return 0. (If it would, then other things would break, just look at how this function is used)." > 15. Bug #51242 (by Sergey Glukhov) > ----------------------------------- > SYNOPSYS > The conjuncts that become false after substitution of the constant > tables are ignored. > > CONCLUSION > The fix should be turned down (but not the test case). > > REASON > See the reasons for turning the fix for bug #52336 that is a > correction for this patch. > > POSSIBLE SOLUTION > See the solution for bug #52336. > 16. Bug #51494 (by Sergey Glukhov) > ----------------------------------- > SYNOPSIS > Crash with explain of a query with outer join > > CONCLUSION > The patch must be turned down. > > REASONS > The patch triggers bug #53334 - a failure of a base join > query for InnoDB. > The bug is actually fixed by the patch for bug #52177 > (see my comment for bug #53334). Yes, it is already reverted in our tree. > 17. Bug #52177 (by Sergey Glukhov) > ----------------------------------- > SYNOPSIS > Crash with exaplain for a query with an outer join. > > CONCLUSION > The fix is correct and the patch should be applied. > The patch also fixes the bug #51494. Ok. > 18. Bug #52336 (by Sergey Glukhov) > ----------------------------------- > SYNOPSYS > A crash caused by an invalid fix for bug #51242. > > CONCLUSION > The patch rather should be turned down. > > REASON. > The patch does not fix the real cause of the problem: > a wrong value is passed as a parameter in the call > Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, > used_tables, > used_tables); > The patch actually suggest a work-around that hides the bug. > This work-around simultaneously adds a new feature. > that catches impossible HAVINGs after constant table substitution. > Yet impossible WHEREs appeared after this optimization remain > uncaught. So the feature is introduced half-baked. > > POSSIBLE SOLUTION > - Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, > - used_tables, > - used_tables); > + Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, > + used_tables, > + (table_map) 0); Ok. This possible solution is not yet applied to our tree (we only discussed it so far).

1 0

[Maria-developers] MariaDB 5.2 source code tree tarball
by Daniel Bartholomew 19 May '10

19 May '10

All, I've created a tarball of a freshly branched MariaDB 5.2 source tree. I created the branch with: bzr branch lp:maria/5.2 mariadb-5.2 I created the tarball with: tar -czvf mariadb-5.2-repo.tar.gz mariadb-5.2/ I then saw to it that the tarball was uploaded to our mirrors. If you are having trouble with using bzr to branch the complete MariaDB source tree, using this tarball is an option. For example, see this bug on Launchpad: https://bugs.launchpad.net/bugs/407834 Links to the tarball (and some brief instructions) are here: http://askmonty.org/wiki/Getting_the_MariaDB_Source_Code#Source_Tree_Tarball I've tested the tree by downloading the tarball to my local machine, and it appears to work just fine. Let me know if you try to use it and it doesn't work for you. Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

1 0

[Maria-developers] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2791)
by Igor Babaev 18 May '10

18 May '10

#At lp:maria/5.2 based on revid:igor@askmonty.org-20100513065914-yq9y2pbd9zn2fm7w 2791 Igor Babaev 2010-05-18 Fixed bugs in the backport of derived tables (mwl106). modified: mysql-test/r/derived_view.result mysql-test/r/table_elim.result sql/item_cmpfunc.cc sql/item_cmpfunc.h sql/sql_class.h sql/sql_select.cc sql/sql_select.h sql/sql_union.cc === modified file 'mysql-test/r/derived_view.result' --- a/mysql-test/r/derived_view.result 2010-04-29 21:10:39 +0000 +++ b/mysql-test/r/derived_view.result 2010-05-18 17:46:32 +0000 @@ -442,7 +442,7 @@ id select_type table type possible_keys 1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` join `test`.`t1` where ((`test`.`t1`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7) and ((`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7))) +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` join `test`.`t1` where ((`test`.`t1`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7)) select * from (select * from (select * from t1 where f1 < 7 ) tt where f1 > 2 ) x === modified file 'mysql-test/r/table_elim.result' --- a/mysql-test/r/table_elim.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/table_elim.result 2010-05-18 17:46:32 +0000 @@ -117,58 +117,58 @@ t2 where id=f.id); This should use one table: explain select id from v1 where id=2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index +1 SIMPLE f const PRIMARY PRIMARY 4 const 1 Using index This should use one table: explain extended select id from v1 where id in (1,2,3,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +1 SIMPLE f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index Warnings: -Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3,4)) This should use facts and a1 tables: explain extended select id from v1 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR -1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index +1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR +1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: -Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14)) This should use facts, a2 and its subquery: explain extended select id from v1 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where; Using MRR -1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index +1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where; Using MRR +1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index Warnings: -Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and (`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `a2`.`id`)))) This should use one table: explain select id from v2 where id=2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index +1 SIMPLE f const PRIMARY PRIMARY 4 const 1 Using index This should use one table: explain extended select id from v2 where id in (1,2,3,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +1 SIMPLE f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index Warnings: -Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3,4)) This should use facts and a1 tables: explain extended select id from v2 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR -1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index +1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR +1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: -Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14)) This should use facts, a2 and its subquery: explain extended select id from v2 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using MRR -1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index +1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using MRR +1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index Warnings: -Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and (`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `f`.`id`)))) drop view v1, v2; drop table t0, t1, t2; === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-04-29 21:10:39 +0000 +++ b/sql/item_cmpfunc.cc 2010-05-18 17:46:32 +0000 @@ -4232,8 +4232,20 @@ Item_cond::fix_fields(THD *thd, Item **r (item= *li.ref())->check_cols(1)) return TRUE; /* purecov: inspected */ used_tables_cache|= item->used_tables(); +#if 0 if (!item->const_item()) const_item_cache= FALSE; +#else + if (item->const_item()) + and_tables_cache= (table_map) 0; + else + { + table_map tmp_table_map= item->not_null_tables(); + not_null_tables_cache|= tmp_table_map; + and_tables_cache&= tmp_table_map; + const_item_cache= FALSE; + } +#endif with_sum_func= with_sum_func || item->with_sum_func; with_subselect|= item->with_subselect; @@ -4253,6 +4265,7 @@ Item_cond::eval_not_null_tables(uchar *o { Item *item; List_iterator<Item> li(list); + and_tables_cache= ~(table_map) 0; while ((item=li++)) { table_map tmp_table_map; === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-04-29 21:10:39 +0000 +++ b/sql/item_cmpfunc.h 2010-05-18 17:46:32 +0000 @@ -1778,19 +1778,6 @@ inline Item *and_conds(Item *a, Item *b) { if (!b) return a; if (!a) return b; - /* Try to minimize item tree by adding to already present AND functions. */ - if (a->type() == Item::COND_ITEM && - ((Item_cond*) a)->functype() == Item_func::COND_AND_FUNC) - { - ((Item_cond*)a)->add(b); - return a; - } - else if (b->type() == Item::COND_ITEM && - ((Item_cond*) b)->functype() == Item_func::COND_AND_FUNC) - { - ((Item_cond*)b)->add(a); - return b; - } return new Item_cond_and(a, b); } === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-04-29 21:10:39 +0000 +++ b/sql/sql_class.h 2010-05-18 17:46:32 +0000 @@ -2790,11 +2790,6 @@ public: */ bool bit_fields_as_long; - /* - Whether to create or postpone actual creation of this temporary table. - TRUE <=> create_tmp_table will create only the TABLE structure. - */ - bool skip_create_table; TMP_TABLE_PARAM() :copy_field(0), group_parts(0), group_length(0), group_null_parts(0), convert_blob_length(0), === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-05-12 04:09:58 +0000 +++ b/sql/sql_select.cc 2010-05-18 17:46:32 +0000 @@ -9845,8 +9845,9 @@ simplify_joins(JOIN *join, List<TABLE_LI { conds= and_conds(conds, table->on_expr); conds->top_level_item(); - if (!conds->fixed) - conds->fix_fields(join->thd, &conds); + /* conds is always a new item as both cond and on_expr existed */ + DBUG_ASSERT(!conds->fixed); + conds->fix_fields(join->thd, &conds); } else conds= table->on_expr; @@ -11035,7 +11036,7 @@ TABLE * create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, - char *table_alias) + char *table_alias, bool do_not_open) { MEM_ROOT *mem_root_save, own_root; TABLE *table; @@ -11728,7 +11729,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA if (thd->is_fatal_error) // If end of memory goto err; /* purecov: inspected */ share->db_record_offset= 1; - if (!param->skip_create_table) + if (!do_not_open) { if (share->db_type() == TMP_ENGINE_HTON) { === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-04-29 21:10:39 +0000 +++ b/sql/sql_select.h 2010-05-18 17:46:32 +0000 @@ -1984,7 +1984,7 @@ void push_index_cond(JOIN_TAB *tab, uint TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, - char* alias); + char* alias, bool do_not_open=FALSE); void free_tmp_table(THD *thd, TABLE *entry); bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, ENGINE_COLUMNDEF *start_recinfo, === modified file 'sql/sql_union.cc' --- a/sql/sql_union.cc 2010-05-12 04:09:58 +0000 +++ b/sql/sql_union.cc 2010-05-18 17:46:32 +0000 @@ -126,12 +126,11 @@ select_union::create_result_table(THD *t tmp_table_param.init(); tmp_table_param.field_count= column_types->elements; tmp_table_param.bit_fields_as_long= bit_fields_as_long; - tmp_table_param.skip_create_table= !create_table; - if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types, (ORDER*) 0, is_union_distinct, 1, - options, HA_POS_ERROR, (char*) alias))) + options, HA_POS_ERROR, (char*) alias, + !create_table))) return TRUE; if (create_table) {

1 0

[Maria-developers] Segmented Key Cache - Syntax section
by Daniel Bartholomew 17 May '10

17 May '10

Hello everyone, Summarizing from my previous email: I'm working on the documentation for the Segmented Key Cache, one of the new features in MariaDB 5.2. My previous email was about the "About" section of the documentation. This email is about the "Syntax" section. For the syntax section, I see there is one new global variable defined for this feature and a new KEY_CACHES table in the information_schema database. Are there any other user-visible items which should be mentioned? Here is a first draft of the syntax section: ---------------------------------------------------------------------- == Segmented Key Cache Syntax == New global variable: key_cache_partitions, it sets the number of segments in a key cache. Valid values for this variable are whole numbers between 0 and 64. If the number of partitions is set to a number greater than 64 the number of partitions will be truncated to 64 and a warning will be issued. A value of '0' means the key cache is a regular (i.e. non-segmented) key cache. This is the default. Other global variables used when working with regular key caches also apply to segmented key caches: key_buffer_size, key_cache_age_threshold, key_cache_block_size, and key_cache_division_limit. See the MySQL manual for descriptions of these variables. http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html Statistics about the key cache can be found by looking at the KEY_CACHES table in the INFORMATION_SCHEMA database. Columns in this table are: * KEY_CACHE_NAME: The name of the key cache * PARTITIONS: total number of segments * PARTITION_NUMBER: segment number (set to NULL if a simple key cache) * FULL_SIZE: memory for cache buffers/auxiliary structures * BLOCK_SIZE: size of the blocks * USED_BLOCKS: number of currently used blocks * UNUSED_BLOCKS: number of currently unused blocks * DIRTY_BLOCKS: number of currently dirty blocks * READ_REQUESTS: number of read requests * READS: number of actual reads from files into buffers * WRITE_REQUESTS: number of write requests * WRITES: number of actual writes from buffers into files ---------------------------------------------------------------------- If there are any factual errors with the above, let me know. Improvements and suggestions are also welcome. Question for the developers: A key_cache_partitions value of '0' means the key cache will not be segmented, it will be a regular (or simple) key cache. A value of '1' means the key cache will be a segmented key cache with a single segment. Is there any benefit to having a "single-segment segmented key cache" compared to a regular "simple key cache" or are they practically the same thing? The key_cache_partitions variable also needs to be documented on the Server System Variables page (http://askmonty.org/wiki/Manual:Server_System_Variables) The entry will look something like this: ---------------------------------------------------------------------- * <code>key_cache_partitions</code> ** '''Description:''' The number of segments in a key cache. ** '''Commandline:''' <code>--key_cache_partitions=#</code> ** '''Scope:''' Global ** '''Dynamic:''' No ** '''Type:''' number ** '''Valid values:''' <code>0-64</code> ** '''Default value:''' <code>0</code> ''(non-segmented)'' * '''Introduced:''' MariaDB 5.2 ---------------------------------------------------------------------- Let me know if there is anything wrong with the above. My task now is to come up with some examples. I'll probably use the test cases for inspiration unless someone has an awesome segmented key cache example that they've been dying to share with me. :) Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

3 3

[Maria-developers] Buildbot Status Updates
by Adam M. Dutko 17 May '10

17 May '10

All of the buildbots I administrate should now be back online (adutko-centos5-amd64, adutko-ultrasparc3 and mariadb-brs). Thank you for your patience. -Adam

1 0