← Back to team overview

maria-developers team mailing list archive

bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2792)

 

#At lp:maria/5.2 based on revid:igor@xxxxxxxxxxxx-20100518174632-e2xaeunykfmtyafm

 2792 Igor Babaev	2010-05-26
      MWL#106: creation of keys for materialized derived tables/views.
      Also fixed several bugs in the backported code.
      modified:
        mysql-test/r/derived_view.result
        sql/item_cmpfunc.cc
        sql/item_subselect.cc
        sql/sql_base.cc
        sql/sql_delete.cc
        sql/sql_insert.cc
        sql/sql_join_cache.cc
        sql/sql_lex.h
        sql/sql_parse.cc
        sql/sql_prepare.cc
        sql/sql_select.cc
        sql/sql_select.h
        sql/table.cc
        sql/table.h

=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result	2010-05-18 17:46:32 +0000
+++ b/mysql-test/r/derived_view.result	2010-05-26 20:04:58 +0000
@@ -556,7 +556,7 @@ test two keys
 explain select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	11	
-1	PRIMARY	<derived2>	ALL	key0	NULL	NULL	NULL	11	Using where; Using join buffer
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	2	Using where
 1	PRIMARY	xx	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	11	Using temporary; Using filesort
 select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-05-18 17:46:32 +0000
+++ b/sql/item_cmpfunc.cc	2010-05-26 20:04:58 +0000
@@ -4232,10 +4232,6 @@ 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
@@ -4245,7 +4241,6 @@ Item_cond::fix_fields(THD *thd, Item **r
       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;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-04-29 21:10:39 +0000
+++ b/sql/item_subselect.cc	2010-05-26 20:04:58 +0000
@@ -2894,6 +2894,9 @@ int subselect_uniquesubquery_engine::exe
     DBUG_RETURN(0);
   }
 
+  if (!tab->preread_init_done && tab->preread_init())
+    DBUG_RETURN(1);
+
   if (null_keypart)
     DBUG_RETURN(scan_table());
  
@@ -3026,7 +3029,7 @@ subselect_uniquesubquery_engine::~subsel
 
 int subselect_indexsubquery_engine::exec()
 {
-  DBUG_ENTER("subselect_indexsubquery_engine::exec");
+  DBUG_ENTER("subselect_indexsubquery_engine");
   int error;
   bool null_finding= 0;
   TABLE *table= tab->table;
@@ -3057,6 +3060,9 @@ int subselect_indexsubquery_engine::exec
     DBUG_RETURN(0);
   }
 
+  if (!tab->preread_init_done && tab->preread_init())
+    DBUG_RETURN(1);
+
   if (null_keypart)
     DBUG_RETURN(scan_table());
 

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2010-05-12 04:09:58 +0000
+++ b/sql/sql_base.cc	2010-05-26 20:04:58 +0000
@@ -6288,7 +6288,9 @@ find_field_in_tables(THD *thd, Item_iden
       find_field_in_table even in the case of information schema tables
       when table_ref->field_translation != NULL.
       */
-    if (table_ref->table && !table_ref->is_merged_derived())
+    if (table_ref->table && 
+        (!table_ref->is_merged_derived() ||
+         (!table_ref->is_multitable() && table_ref->merged_for_insert)))
       found= find_field_in_table(thd, table_ref->table, name, length,
                                  TRUE, &(item->cached_field_index));
     else

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2010-05-12 04:09:58 +0000
+++ b/sql/sql_delete.cc	2010-05-26 20:04:58 +0000
@@ -559,6 +559,11 @@ int mysql_multi_delete_prepare(THD *thd)
   TABLE_LIST *target_tbl;
   DBUG_ENTER("mysql_multi_delete_prepare");
 
+  TABLE_LIST *tables= lex->query_tables;
+  if (mysql_handle_derived(lex, DT_INIT) ||
+      mysql_handle_list_of_derived(lex, tables, DT_MERGE_FOR_INSERT) ||
+      mysql_handle_list_of_derived(lex, tables, DT_PREPARE))
+    DBUG_RETURN(TRUE);
   /*
     setup_tables() need for VIEWs. JOIN::prepare() will not do it second
     time.

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2010-04-29 21:10:39 +0000
+++ b/sql/sql_insert.cc	2010-05-26 20:04:58 +0000
@@ -1184,8 +1184,8 @@ static bool mysql_prepare_insert_check_t
   if (insert_into_view && !fields.elements)
   {
     thd->lex->empty_field_list_on_rset= 1;
-    if (table_list->is_multitable() && !table_list->table ||
-        !table_list->table->created) 
+    if (!thd->lex->select_lex.leaf_tables.head()->table ||
+        table_list->is_multitable())
     {
       my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0),
                table_list->view_db.str, table_list->view_name.str);
@@ -1276,8 +1276,10 @@ bool mysql_prepare_insert(THD *thd, TABL
   /* INSERT should have a SELECT or VALUES clause */
   DBUG_ASSERT (!select_insert || !values);
 
+  if (mysql_handle_derived(thd->lex, DT_INIT))
+    DBUG_RETURN(TRUE); 
   if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT))
-    DBUG_RETURN(TRUE);  
+    DBUG_RETURN(TRUE); 
   if (mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE))
     DBUG_RETURN(TRUE); 
   /*

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2010-03-07 15:41:45 +0000
+++ b/sql/sql_join_cache.cc	2010-05-26 20:04:58 +0000
@@ -2370,6 +2370,8 @@ JOIN_CACHE_BKA::init_join_matching_recor
 
   init_mrr_buff();
 
+  if (!join_tab->preread_init_done && join_tab->preread_init())
+    return NESTED_LOOP_ERROR;
   /* 
     Prepare to iterate over keys from the join buffer and to get
     matching candidates obtained with MMR handler functions.

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2010-04-29 21:10:39 +0000
+++ b/sql/sql_lex.h	2010-05-26 20:04:58 +0000
@@ -1873,6 +1873,8 @@ typedef struct st_lex : public Query_tab
     switch (sql_command) {
     case SQLCOM_UPDATE:
     case SQLCOM_UPDATE_MULTI:
+    case SQLCOM_DELETE:
+    case SQLCOM_DELETE_MULTI:
     case SQLCOM_INSERT:
     case SQLCOM_INSERT_SELECT:
     case SQLCOM_REPLACE:

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2010-04-29 21:10:39 +0000
+++ b/sql/sql_parse.cc	2010-05-26 20:04:58 +0000
@@ -3425,9 +3425,6 @@ end_with_restore_list:
     thd_proc_info(thd, "init");
     if ((res= open_and_lock_tables(thd, all_tables)))
       break;
-    if (mysql_handle_list_of_derived(lex, all_tables, DT_MERGE_FOR_INSERT) ||
-        mysql_handle_list_of_derived(lex, all_tables, DT_PREPARE))
-      DBUG_RETURN(1);
 
     if ((res= mysql_multi_delete_prepare(thd)))
       goto error;

=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc	2010-04-29 21:10:39 +0000
+++ b/sql/sql_prepare.cc	2010-05-26 20:04:58 +0000
@@ -1133,9 +1133,7 @@ static bool mysql_test_insert(Prepared_s
     If we would use locks, then we have to ensure we are not using
     TL_WRITE_DELAYED as having two such locks can cause table corruption.
   */
-  if (open_normal_and_derived_tables(thd, table_list, 0,
-                                     DT_INIT | DT_PREPARE | DT_CREATE) ||
-      mysql_handle_single_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT))
+  if (open_normal_and_derived_tables(thd, table_list, 0, DT_INIT)) 
     goto error;
 
   if ((values= its++))
@@ -1236,9 +1234,16 @@ static int mysql_test_update(Prepared_st
     thd->fill_derived_tables() is false here for sure (because it is
     preparation of PS, so we even do not check it).
   */
-  if (mysql_handle_derived(thd->lex, DT_PREPARE))
+  if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT) ||
+      table_list->handle_derived(thd->lex, DT_PREPARE))
     goto error;
 
+  if (!table_list->updatable)
+  {
+    my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
+    goto error;
+  }
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   /* Force privilege re-checking for views after they have been opened. */
   want_privilege= (table_list->view ? UPDATE_ACL :
@@ -1291,13 +1296,18 @@ error:
 static bool mysql_test_delete(Prepared_statement *stmt,
                               TABLE_LIST *table_list)
 {
+  uint table_count= 0;
   THD *thd= stmt->thd;
   LEX *lex= stmt->lex;
   DBUG_ENTER("mysql_test_delete");
 
   if (delete_precheck(thd, table_list) ||
-      open_normal_and_derived_tables(thd, table_list, 0,
-                                     DT_PREPARE | DT_CREATE))
+      open_tables(thd, &table_list, &table_count, 0))
+    goto error;
+
+  if (mysql_handle_derived(thd->lex, DT_INIT) ||
+      mysql_handle_list_of_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT) ||
+      mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE))
     goto error;
 
   if (!table_list->table)
@@ -1561,6 +1571,7 @@ select_like_stmt_test_with_open(Prepared
                                 int (*specific_prepare)(THD *thd),
                                 ulong setup_tables_done_option)
 {
+  uint table_count= 0;
   DBUG_ENTER("select_like_stmt_test_with_open");
 
   /*
@@ -1569,8 +1580,8 @@ select_like_stmt_test_with_open(Prepared
     prepared EXPLAIN yet so derived tables will clean up after
     themself.
   */
-  if (open_normal_and_derived_tables(stmt->thd, tables, 0,
-                                     DT_PREPARE | DT_CREATE))
+  THD *thd= stmt->thd;
+  if (open_tables(thd, &tables, &table_count, 0))
     DBUG_RETURN(TRUE);
 
   DBUG_RETURN(select_like_stmt_test(stmt, specific_prepare,

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-05-18 17:46:32 +0000
+++ b/sql/sql_select.cc	2010-05-26 20:04:58 +0000
@@ -238,7 +238,7 @@ static void add_group_and_distinct_keys(
 void get_partial_join_cost(JOIN *join, uint idx, double *read_time_arg,
                            double *record_count_arg);
 static uint make_join_orderinfo(JOIN *join);
-static bool generate_derived_keys(List<TABLE_LIST> &tables);
+static bool generate_derived_keys(DYNAMIC_ARRAY *keyuse_array);
 static int
 join_read_record_no_init(JOIN_TAB *tab);
 
@@ -744,18 +744,6 @@ JOIN::optimize()
   
   tables= select_lex->leaf_tables.elements;
 
-#if 0
-  if (thd->lex->describe)
-  {
-    /*
-      Force join->join_tmp creation, because we will use this JOIN
-      twice for EXPLAIN and we have to have unchanged join for EXPLAINing
-    */
-    select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
-    select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
-  }
-#else
-#endif
 
   if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */
     DBUG_RETURN(-1);
@@ -3311,18 +3299,20 @@ add_key_field(KEY_FIELD **key_fields,uin
               Field *field, bool eq_func, Item **value, uint num_values,
               table_map usable_tables, SARGABLE_PARAM **sargables)
 {
-  uint exists_optimize= 0;
-  if (field->table->pos_in_table_list->is_materialized_derived() &&
+  uint optimize= 0;
+  if (eq_func && 
+      field->table->pos_in_table_list->is_materialized_derived() &&
       !field->table->created)
-    field->table->pos_in_table_list->update_derived_keys(field, value,
-                                                         num_values);
-  if (!(field->flags & PART_KEY_FLAG))
+  {
+    optimize= KEY_OPTIMIZE_EQ;
+  }   
+  else if (!(field->flags & PART_KEY_FLAG))
   {
     // Don't remove column IS NULL on a LEFT JOIN table
     if (!eq_func || (*value)->type() != Item::NULL_ITEM ||
         !field->table->maybe_null || field->null_ptr)
       return;					// Not a key. Skip it
-    exists_optimize= KEY_OPTIMIZE_EXISTS;
+    optimize= KEY_OPTIMIZE_EXISTS;
     DBUG_ASSERT(num_values == 1);
   }
   else
@@ -3342,7 +3332,7 @@ add_key_field(KEY_FIELD **key_fields,uin
       if (!eq_func || (*value)->type() != Item::NULL_ITEM ||
           !field->table->maybe_null || field->null_ptr)
 	return;					// Can't use left join optimize
-      exists_optimize= KEY_OPTIMIZE_EXISTS;
+      optimize= KEY_OPTIMIZE_EXISTS;
     }
     else
     {
@@ -3441,7 +3431,7 @@ add_key_field(KEY_FIELD **key_fields,uin
   (*key_fields)->eq_func=	eq_func;
   (*key_fields)->val=		*value;
   (*key_fields)->level=		and_level;
-  (*key_fields)->optimize=	exists_optimize;
+  (*key_fields)->optimize=	optimize;
   /*
     If the condition has form "tbl.keypart = othertbl.field" and 
     othertbl.field can be NULL, there will be no matches if othertbl.field 
@@ -3760,6 +3750,34 @@ max_part_bit(key_part_map bits)
   return found;
 }
 
+static bool
+add_keyuse(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field,
+          uint key, uint part)
+{
+  KEYUSE keyuse;
+  Field *field= key_field->field;
+
+  keyuse.table= field->table;
+  keyuse.val= key_field->val;
+  keyuse.key= key;
+  if (key != MAX_KEY)
+  {
+    keyuse.keypart=part;
+    keyuse.keypart_map= (key_part_map) 1 << part;
+  }
+  else
+  {
+    keyuse.keypart= field->field_index;
+    keyuse.keypart_map= (key_part_map) 0;
+  }
+  keyuse.used_tables= key_field->val->used_tables();
+  keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
+  keyuse.null_rejecting= key_field->null_rejecting;
+  keyuse.cond_guard= key_field->cond_guard;
+  keyuse.sj_pred_no= key_field->sj_pred_no;
+  return (insert_dynamic(keyuse_array,(uchar*) &keyuse));
+}
+
 /*
   Add all keys with uses 'field' for some keypart
   If field->and_level != and_level then only mark key_part as const_part
@@ -3774,10 +3792,13 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array
 {
   Field *field=key_field->field;
   TABLE *form= field->table;
-  KEYUSE keyuse;
 
   if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS))
   {
+    if (key_field->eq_func && (key_field->optimize & KEY_OPTIMIZE_EQ))
+    {
+      return add_keyuse(keyuse_array, key_field, MAX_KEY, 0);
+    }
     for (uint key=0 ; key < form->s->keys ; key++)
     {
       if (!(form->keys_in_use_for_query.is_set(key)))
@@ -3790,17 +3811,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array
       {
 	if (field->eq(form->key_info[key].key_part[part].field))
 	{
-	  keyuse.table= field->table;
-	  keyuse.val =  key_field->val;
-	  keyuse.key =  key;
-	  keyuse.keypart=part;
-	  keyuse.keypart_map= (key_part_map) 1 << part;
-	  keyuse.used_tables=key_field->val->used_tables();
-	  keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
-          keyuse.null_rejecting= key_field->null_rejecting;
-          keyuse.cond_guard= key_field->cond_guard;
-          keyuse.sj_pred_no= key_field->sj_pred_no;
-	  if (insert_dynamic(keyuse_array,(uchar*) &keyuse))
+          if (add_keyuse(keyuse_array, key_field, key, part))
             return TRUE;
 	}
       }
@@ -3885,6 +3896,9 @@ sort_keyuse(KEYUSE *a,KEYUSE *b)
     return (int) (a->table->tablenr - b->table->tablenr);
   if (a->key != b->key)
     return (int) (a->key - b->key);
+  if (a->key == MAX_KEY && b->key == MAX_KEY && 
+      a->used_tables != b->used_tables)
+    return (int) ((ulong) a->used_tables - (ulong) b->used_tables);
   if (a->keypart != b->keypart)
     return (int) (a->keypart - b->keypart);
   // Place const values before other ones
@@ -4080,9 +4094,6 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR
     }
   }
 
-  /* Generate keys descriptions for derived tables */
-  generate_derived_keys(select_lex->leaf_tables);
-
   /* fill keyuse with found key parts */
   for ( ; field != end ; field++)
   {
@@ -4117,6 +4128,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR
     if (insert_dynamic(keyuse,(uchar*) &key_end))
       return TRUE;
 
+    generate_derived_keys(keyuse);
+
     use=save_pos=dynamic_element(keyuse,0,KEYUSE*);
     prev= &key_end;
     found_eq_constant=0;
@@ -6979,33 +6992,93 @@ make_join_select(JOIN *join,SQL_SELECT *
 }
 
 
-/**
-  @brief
-  Add keys to derived tables'/views' result tables in a list
-
-  @param tables list of tables to generate keys for
-
-  @details
-  This function generates keys for all derived tables/views in the 'tables'
-  list with help of the TABLE_LIST:generate_keys function.
+static
+uint get_next_field_for_derived_key(uchar *arg)
+{
+  KEYUSE *keyuse= *(KEYUSE **) arg;
+  if (!keyuse)
+    return (uint) (-1);
+  uint key= keyuse->key;
+  uint fldno= keyuse->keypart; 
+  uint keypart= keyuse->keypart_map == (key_part_map) 1 ?
+                                         0 : (keyuse-1)->keypart+1;
+  for ( ; keyuse->key == key && keyuse->keypart == fldno; keyuse++)
+    keyuse->keypart= keypart;
+  if (keyuse->key != key)
+    keyuse= 0;
+  return fldno;
+}
 
-  @note currently this function can't fail because errors from the
-  TABLE_LIST:generate_keys function is ignored as they aren't critical to the
-  query execution.
 
-  @return FALSE all keys were successfully added.
-*/
+static 
+bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys)
+{
+  TABLE *table= keyuse->table;
+  if (table->alloc_keys(keys))
+    return TRUE;
+  uint keyno= 0;
+  KEYUSE *first_keyuse= keyuse;
+  uint prev_part= (uint) (-1);
+  uint parts= 0;
+  uint i= 0;
+  do
+  {
+    keyuse->key= keyno;
+    keyuse->keypart_map= (key_part_map) (1 << parts);
+    keyuse++;
+    if (++i == count || keyuse->used_tables != first_keyuse->used_tables)
+    {
+      if (table->add_tmp_key(keyno, ++parts, 
+                             get_next_field_for_derived_key, 
+                             (uchar *) &first_keyuse))
+        return TRUE;
+      first_keyuse= keyuse;
+      keyno++;
+      parts= 0;
+    }
+    else if (keyuse->keypart != prev_part)
+    {
+      parts++;
+      prev_part= keyuse->keypart;
+    }
+  } while (keyno < keys);
+  return FALSE;
+}
+   
 
 static
-bool generate_derived_keys(List<TABLE_LIST> &tables)
+bool generate_derived_keys(DYNAMIC_ARRAY *keyuse_array)
 {
-  TABLE_LIST *table;
-  List_iterator<TABLE_LIST> ti(tables);
-  while ((table= ti++))
+  KEYUSE *keyuse= dynamic_element(keyuse_array, 0, KEYUSE*);
+  uint elements= keyuse_array->elements;
+  TABLE *prev_table= 0;
+  for (uint i= 0; i < elements; i++, keyuse++)
   {
-    /* Process tables that aren't materialized yet. */
-    if (table->is_materialized_derived() && !table->table->created)
-      table->generate_keys();
+    KEYUSE *first_table_keyuse;
+    table_map last_used_tables;
+    uint count;
+    uint keys;
+    while (keyuse->key == MAX_KEY)
+    {
+      if (keyuse->table != prev_table)
+      {
+        prev_table= keyuse->table;
+        first_table_keyuse= keyuse;
+        last_used_tables= keyuse->used_tables;
+        count= 0;
+        keys= 0;
+      }
+      else if (keyuse->used_tables != last_used_tables)
+      {
+        keys++;
+        last_used_tables= keyuse->used_tables;
+      }
+      count++;
+      keyuse++;
+      if (keyuse->table != prev_table &&
+          generate_derived_keys_for_table(first_table_keyuse, count, ++keys))
+        return TRUE;
+    }
   }
   return FALSE;
 }

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-05-18 17:46:32 +0000
+++ b/sql/sql_select.h	2010-05-26 20:04:58 +0000
@@ -37,6 +37,7 @@
 /* Values in optimize */
 #define KEY_OPTIMIZE_EXISTS		1
 #define KEY_OPTIMIZE_REF_OR_NULL	2
+#define KEY_OPTIMIZE_EQ	                4
 
 typedef struct keyuse_t {
   TABLE *table;

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2010-05-12 04:09:58 +0000
+++ b/sql/table.cc	2010-05-26 20:04:58 +0000
@@ -5143,12 +5143,11 @@ void st_table::mark_virtual_columns_for_
 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));
+  key_info= s->key_info= (KEY*) alloc_root(&mem_root, sizeof(KEY)*key_count);
   max_keys= key_count;
   return !(key_info);
 }
 
-
 /**
   @brief Adds one key to a temporary table.
 
@@ -5164,40 +5163,41 @@ bool TABLE::alloc_keys(uint key_count)
   @return >=0 number of newly added key.
 */
 
-int TABLE::add_tmp_key(ulonglong key_parts, char *key_name)
+bool TABLE::add_tmp_key(uint key, uint key_parts,
+                        uint (*next_field_no) (uchar *), uchar *arg)
 {
-  DBUG_ASSERT(!created && s->keys< max_keys);
+  DBUG_ASSERT(!created && key < max_keys);
 
+  char buf[NAME_CHAR_LEN];
   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));
+      (KEY_PART_INFO*) alloc_root(&mem_root, sizeof(KEY_PART_INFO)*key_parts);
   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;
+    return TRUE;
+  keyinfo= key_info + key;
+  keyinfo->key_part= key_part_info;
+  keyinfo->usable_key_parts= keyinfo->key_parts = key_parts;
   keyinfo->key_length=0;
   keyinfo->algorithm= HA_KEY_ALG_UNDEF;
-  keyinfo->name= key_name;
   keyinfo->flags= HA_GENERATED_KEY;
-  keyinfo->rec_per_key= (ulong*)my_malloc(sizeof(ulong)*key_part_count, MYF(0));
+  sprintf(buf, "key%i", key);
+  if (!(keyinfo->name= strdup_root(&mem_root, buf)))
+    return TRUE;
+  keyinfo->rec_per_key= (ulong*) alloc_root(&mem_root,
+                                            sizeof(ulong)*key_parts);
   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++)
+    return TRUE;
+  bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts);
+  for (i= 0; i < key_parts; i++)
   {
-    if (!(key_parts & (1 << i)))
-      continue;
+    reg_field= field + next_field_no(arg);
     if (key_start)
-      (*reg_field)->key_start.set_bit(s->keys);
+      (*reg_field)->key_start.set_bit(key);
     key_start= FALSE;
-    (*reg_field)->part_of_key.set_bit(s->keys);
+      (*reg_field)->part_of_key.set_bit(key);
     (*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 -
@@ -5231,10 +5231,10 @@ int TABLE::add_tmp_key(ulonglong key_par
     key_part_info++;
   }
   set_if_bigger(s->max_key_length, keyinfo->key_length);
-  return ++s->keys - 1;
+  s->keys++;
+  return FALSE;
 }
 
-
 /*
   @brief
   Drop all indexes except specified one.
@@ -5310,6 +5310,7 @@ void TABLE_LIST::reinit_before_use(THD *
          parent_embedding->nested_join->join_list.head() == embedded);
 }
 
+
 /*
   Return subselect that contains the FROM list this table is taken from
 
@@ -5819,143 +5820,8 @@ int TABLE_LIST::fetch_number_of_rows()
                   t1 ON tt.f1=a_function(t1.f3);
   In this case for the derived table tt one key will be generated. It will
   consist of one field - f1.
-
-  Implementation is split in two steps:
-    gathering information on all used fields of derived tables/view and
-      store it in lists of possible keys, one per a derived table/view.
-    add keys to result tables of derived tables/view using info from above
-      lists.
-
-  The above procedure is implemented in 4 functions:
-    TABLE_LIST::update_derived_keys
-                          Create/extend list of possible keys for one derived
-                          table/view based on given field/used tables info.
-                          (Step one)
-    generate_derived_keys This function is called at the moment when all
-                          possible info on keys is gathered and it's safe to
-                          add keys. Walk over list of derived tables/views and
-                          calls to TABLE_LIST::generate_keys to actually
-                          generate keys. (Step two)
-    TABLE_LIST::generate_keys
-                          Walks over list of possible keys for this derived
-                          table/view to add keys to the result table.
-                          Calls to TABLE::add_tmp_index to actually add
-                          keys. (Step two)
-    TABLE::add_tmp_index  Creates one index description according to given
-                          bitmap of used fields. (Step two)
-  There is also the fifth function called TABLE::use_index. It saves used
-  key and frees others. It is called when the optimizer has chosen which key
-  it will use, thus we don't need other keys anymore.
-*/
-
-
-/*
-  @brief
-  Update derived table's list of possible keys
-
-  @param field      derived table's field to take part in a key
-  @param values     array of values
-  @param num_values number of elements in the array values
-
-  @details
-  This function creates/extends a list of possible keys for this derived
-  table/view. For each table used by a value from the 'values' array the
-  corresponding possible key is extended to include the 'field'.
-  If there is no such possible key then it is created. field's
-  key_start/part_of_key bitmaps are updated accordingly.
-
-  @return TRUE  new possible key can't be allocated.
-  @return FALSE list of possible keys successfully updated.
-*/
-
-bool TABLE_LIST::update_derived_keys(Field *field, Item **values,
-                                     uint num_values)
-{
-  DERIVED_KEY_MAP *entry= 0;
-  List_iterator<DERIVED_KEY_MAP> ki(derived_keymap_list);
-  uint i;
-
-  /* Allow all keys to be used. */
-  if (!derived_keymap_list.elements)
-  {
-    table->keys_in_use_for_query.set_all();
-    table->s->uniques= 0;
-    derived_keymap_list.empty();
-  }
-
-  for (i= 0; i < num_values; i++)
-  {
-    uint tbl;
-    table_map tables= values[i]->used_tables() & ~OUTER_REF_TABLE_BIT;
-    for (tbl= 1; tables >= tbl; tbl<<= 1)
-    {
-      uint key= 0;
-      if (! (tables & tbl))
-        continue;
-      ki.rewind();
-      while ((entry= ki++))
-      {
-        key++;
-        if (entry->referenced_by & tbl)
-          break;
-      }
-      if (!entry)
-      {
-        key++;
-        entry= (DERIVED_KEY_MAP*)my_malloc(sizeof(DERIVED_KEY_MAP), MYF(0));
-        if (!entry)
-          return TRUE;
-        entry->referenced_by|= tbl;
-        entry->used_fields.clear_all();
-        derived_keymap_list.push_back(entry);
-        field->key_start.set_bit(key);
-        table->max_keys++;
-      }
-      field->part_of_key.set_bit(key - 1);
-      field->flags|= PART_KEY_FLAG;
-      entry->used_fields.set_bit(field->field_index);
-    }
-  }
-  return FALSE;
-}
-
-
-/**
-  @brief
-  Generate keys for a materialized derived table/view
-
-  @details
-  This function adds keys to the result table by walking over the list of
-  possible keys for this derived table/view and calling to the
-  TABLE::add_tmp_index to actually add keys. A name "key" with a sequential
-  number is given to each key to ease debugging.
-
-  @return TRUE  an error occur.
-  @return FALSE all keys were successfully added.
 */
 
-bool TABLE_LIST::generate_keys()
-{
-  List_iterator<DERIVED_KEY_MAP> it(derived_keymap_list);
-  DERIVED_KEY_MAP *entry;
-  uint key= 0;
-  char buf[NAME_CHAR_LEN];
-  DBUG_ASSERT(is_materialized_derived());
-
-  if (!derived_keymap_list.elements)
-    return FALSE;
-
-  table->alloc_keys(table->max_keys);
-  while ((entry= it++))
-  {
-    table->s->key_parts+= entry->used_fields.bits_set();
-    sprintf(buf, "key%i", key++);
-    if (table->add_tmp_key(entry->used_fields.to_ulonglong(),
-                           table->in_use->strdup(buf)) < 0)
-      return TRUE;
-  }
-  return FALSE;
-}
 
 
 /*

=== modified file 'sql/table.h'
--- a/sql/table.h	2010-04-29 21:10:39 +0000
+++ b/sql/table.h	2010-05-26 20:04:58 +0000
@@ -916,7 +916,8 @@ struct st_table {
   inline bool needs_reopen_or_name_lock()
   { return s->version != refresh_version; }
   bool alloc_keys(uint key_count);
-  int add_tmp_key(ulonglong key_parts, char *key_name);
+  bool add_tmp_key(uint key, uint key_parts,
+                   uint (*next_field_no) (uchar *), uchar *arg);
   void use_index(int key_to_save);
   void set_table_map(table_map map_arg, uint tablenr_arg)
   {
@@ -1202,20 +1203,6 @@ class Item_in_subselect;
          (TABLE_LIST::join_using_fields != NULL)
 */
 
-/*
-  This structure is used to keep info about possible key for the result table
-  of a derived table/view.
-  The 'referenced_by' is the table map of the table to which this possible
-    key corresponds.
-  The 'used_field' is a map of fields of which this key consists of.
-  See also the comment for the TABLE_LIST::update_derived_keys function.
-*/
-struct st_derived_table_key_map {
-  table_map referenced_by;
-  key_map used_fields;
-};
-typedef st_derived_table_key_map DERIVED_KEY_MAP;
-
 class Index_hint;
 struct st_lex;
 struct TABLE_LIST
@@ -1531,8 +1518,6 @@ struct TABLE_LIST
   uint table_open_method;
   enum enum_schema_table_state schema_table_state;
 
-  List<DERIVED_KEY_MAP> derived_keymap_list;
-
   void calc_md5(char *buffer);
   int view_check_option(THD *thd, bool ignore_failure);
   bool create_field_translation(THD *thd);
@@ -1709,8 +1694,6 @@ struct TABLE_LIST
   void wrap_into_nested_join(List<TABLE_LIST> &join_list);
   bool init_derived(THD *thd, bool init_view);
   int fetch_number_of_rows();
-  bool update_derived_keys(Field *field, Item **values, uint num_values);
-  bool generate_keys();
   bool change_refs_to_fields();
 
 private: