Many files:

  Added key cache assignment
mi_locking.c:
  Added key cache assignment: correction
my_sys.h:
  Added key cache variable structure


include/my_sys.h:
  Added key cache variable structure
include/my_base.h:
  Added key cache assignment
include/myisam.h:
  Added key cache assignment
include/my_global.h:
  Added key cache assignment
isam/_page.c:
  Added key cache assignment
isam/close.c:
  Added key cache assignment
isam/isamchk.c:
  Added key cache assignment
isam/isamlog.c:
  Added key cache assignment
isam/panic.c:
  Added key cache assignment
isam/_locking.c:
  Added key cache assignment
isam/test2.c:
  Added key cache assignment
isam/test3.c:
  Added key cache assignment
myisam/myisamdef.h:
  Added key cache assignment
myisam/mi_check.c:
  Added key cache assignment
myisam/mi_close.c:
  Added key cache assignment
myisam/mi_extra.c:
  Added key cache assignment
myisam/mi_page.c:
  Added key cache assignment
myisam/mi_panic.c:
  Added key cache assignment
myisam/mi_preload.c:
  Added key cache assignment
myisam/mi_test1.c:
  Added key cache assignment
myisam/mi_test2.c:
  Added key cache assignment
myisam/mi_test3.c:
  Added key cache assignment
myisam/myisamchk.c:
  Added key cache assignment
myisam/myisamlog.c:
  Added key cache assignment
myisam/mi_delete_all.c:
  Added key cache assignment
myisam/mi_locking.c:
  Added key cache assignment: correction
myisam/mi_keycache.c:
  Added key cache assignment
sql/handler.h:
  Added key cache assignment
sql/mysql_priv.h:
  Added key cache assignment
sql/set_var.h:
  Added key cache assignment
sql/table.h:
  Added key cache assignment
sql/ha_myisam.cc:
  Added key cache assignment
sql/ha_myisammrg.cc:
  Added key cache assignment
sql/handler.cc:
  Added key cache assignment
sql/mysqld.cc:
  Added key cache assignment
sql/set_var.cc:
  Added key cache assignment
sql/sql_base.cc:
  Added key cache assignment
sql/sql_table.cc:
  Added key cache assignment
sql/sql_test.cc:
  Added key cache assignment
sql/sql_yacc.yy:
  Added key cache assignment
mysys/mf_keycache.c:
  Added key cache assignment
mysql-test/t/key_cache.test:
  Added key cache assignment
mysql-test/r/key_cache.result:
  Added key cache assignment

13 files changed, 571 insertions, 109 deletions

diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc
index 4ca11fe0da5..85d5236a18f 100644
--- a/sql/ha_myisam.cc
+++ b/sql/ha_myisam.cc
@@ -228,6 +228,12 @@ int ha_myisam::open(const char *name, int mode, uint test_if_locked)
 {
   if (!(file=mi_open(name, mode, test_if_locked)))
     return (my_errno ? my_errno : -1);
+  
+  /* Synchronize key cache assignment of the handler */ 
+  KEY_CACHE_VAR *key_cache= table->key_cache ? table->key_cache :
+                                               &dflt_key_cache_var;
+  VOID(mi_extra(file, HA_EXTRA_SET_KEY_CACHE,
+                (void*) &key_cache->cache));
 
   if (test_if_locked & (HA_OPEN_IGNORE_IF_LOCKED | HA_OPEN_TMP_TABLE))
     VOID(mi_extra(file, HA_EXTRA_NO_WAIT_LOCK, 0));
@@ -694,16 +700,27 @@ int ha_myisam::repair(THD *thd, MI_CHECK &param, bool optimize)
 
 int ha_myisam::assign_to_keycache(THD* thd, HA_CHECK_OPT *check_opt)
 {
-  int error;
-  const char *errmsg;
+  uint len;
+  KEY_CACHE_VAR *old_key_cache;
+  KEY_CACHE_VAR *new_key_cache;                           
+  const char *errmsg=0;
+  int error= HA_ADMIN_OK;
   ulonglong map= ~(ulonglong) 0;
   TABLE_LIST *table_list= table->pos_in_table_list;
-  char *keycache_name= table_list->option;
-
+  const char *new_key_cache_name= table_list->option ?
+                                  (const char *) table_list->option :
+                                  DEFAULT_KEY_CACHE_NAME;
+  KEY_CACHE_ASMT *key_cache_asmt= table->key_cache_asmt;
+  bool triggered= key_cache_asmt->triggered; 
+  
   DBUG_ENTER("ha_myisam::assign_to_keycache");
 
+  VOID(pthread_mutex_lock(&LOCK_assign));
+
+  old_key_cache= key_cache_asmt->key_cache;
+
   /* Check validity of the index references */ 
-  if (table_list->use_index)
+  if (!triggered && table_list->use_index)
   {
     key_map kmap= get_key_map_from_key_list(table, table_list->use_index);
     if (kmap == ~(key_map) 0)
@@ -715,23 +732,54 @@ int ha_myisam::assign_to_keycache(THD* thd, HA_CHECK_OPT *check_opt)
     if (kmap)
       map= kmap;
   }
-  
-  if ((error= mi_assign_to_keycache(file, map, keycache_name)))
+
+  len= strlen(new_key_cache_name);
+  new_key_cache= get_or_create_key_cache(new_key_cache_name, len);
+  if (old_key_cache == new_key_cache)
+  {
+    /* Nothing to do: table is assigned to the same key cache */
+    goto ok;
+  }
+                                                         
+  if (!new_key_cache ||
+      (!new_key_cache->cache && ha_key_cache(new_key_cache)))  
   {
+    if (key_cache_asmt->triggered)
+      error= HA_ERR_OUT_OF_MEM;
+    else         
+    { 
+      char buf[ERRMSGSIZE];
+      my_snprintf(buf, ERRMSGSIZE,
+                  "Failed to create key cache %s", new_key_cache_name);
+      errmsg= buf;
+      error= HA_ADMIN_FAILED;
+    }
+    goto err;
+  }
+
+  reassign_key_cache(key_cache_asmt, new_key_cache);
+       
+  VOID(pthread_mutex_unlock(&LOCK_assign));
+  error= mi_assign_to_keycache(file, map, &new_key_cache->cache);
+  VOID(pthread_mutex_lock(&LOCK_assign));
+
+  if (error && !key_cache_asmt->triggered)
+  { 
     switch (error) {
     default: 
       char buf[ERRMSGSIZE+20];
       my_snprintf(buf, ERRMSGSIZE, 
-                  "Failed to read from index file (errno: %d)", my_errno);
+                  "Failed to flush to index file (errno: %d)", my_errno);
       errmsg= buf;
     }
-    error= HA_ADMIN_FAILED;
+    error= HA_ADMIN_CORRUPT;
     goto err;
   }
-  
-  DBUG_RETURN(HA_ADMIN_OK);
+
+  goto ok;
 
  err:
+  if (!triggered)
   {
     MI_CHECK param;
     myisamchk_init(&param);
@@ -741,8 +789,33 @@ int ha_myisam::assign_to_keycache(THD* thd, HA_CHECK_OPT *check_opt)
     param.table_name= table->table_name;
     param.testflag= 0;
     mi_check_print_error(&param, errmsg);
-    DBUG_RETURN(error);
+ }
+
+ ok:
+  if (--key_cache_asmt->requests)
+  {
+    /* There is a queue of assignments for the table */
+
+    /* Remove the first member from the queue */
+    struct st_my_thread_var *last= key_cache_asmt->queue;
+    struct st_my_thread_var *thread= last->next;
+    if (thread->next == thread)
+      key_cache_asmt->queue= 0;
+    else
+    {
+      last->next= thread->next;
+      last->next->prev= &last->next;
+      thread->next= 0;
+    }
+    /* Signal the first waiting thread to proceed */
+    VOID(pthread_cond_signal(&thread->suspend));
   }
+
+  key_cache_asmt->triggered= 0;
+
+  VOID(pthread_mutex_unlock(&LOCK_assign));
+
+  DBUG_RETURN(error);
 }
 
 
diff --git a/sql/ha_myisammrg.cc b/sql/ha_myisammrg.cc
index a0449e83222..d6d4f58b31c 100644
--- a/sql/ha_myisammrg.cc
+++ b/sql/ha_myisammrg.cc
@@ -45,6 +45,12 @@ int ha_myisammrg::open(const char *name, int mode, uint test_if_locked)
     DBUG_PRINT("info", ("ha_myisammrg::open exit %d", my_errno));
     return (my_errno ? my_errno : -1);
   }
+  /* Synchronize key cache assignment for the file */
+  KEY_CACHE_VAR *key_cache= table->key_cache ? table->key_cache :
+                                               &dflt_key_cache_var;
+  VOID(myrg_extra(file, HA_EXTRA_SET_KEY_CACHE,
+                  (void*) &key_cache->cache));
+
   DBUG_PRINT("info", ("ha_myisammrg::open myrg_extrafunc..."))
   myrg_extrafunc(file, query_cache_invalidate_by_MyISAM_filename_ref);
   if (!(test_if_locked == HA_OPEN_WAIT_IF_LOCKED ||
diff --git a/sql/handler.cc b/sql/handler.cc
index 049194a18db..1063066f588 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -984,17 +984,41 @@ int ha_create_table(const char *name, HA_CREATE_INFO *create_info,
 
 	/* Use key cacheing on all databases */
 
-void ha_key_cache(void)
+int ha_key_cache(KEY_CACHE_VAR *key_cache)
 {
-  if (keybuff_size)
-    (void) init_key_cache(&dflt_keycache,dflt_key_block_size,
-                          (ulong) keybuff_size);
+  if (!key_cache->cache)
+  {
+    if (!key_cache->block_size)
+      key_cache->block_size= dflt_key_cache_block_size;
+    if (!key_cache->buff_size)
+      key_cache->buff_size= dflt_key_buff_size;
+    return !init_key_cache( &key_cache->cache,
+			   key_cache->block_size,
+			   key_cache->buff_size,
+                           key_cache);
+  }
+  return 0;
 }
 
 
-void ha_resize_key_cache(void)
+int ha_resize_key_cache(KEY_CACHE_VAR *key_cache)
+{
+  if (key_cache->cache)
+  {
+    return !resize_key_cache(&key_cache->cache, 
+                           key_cache->buff_size);
+  }
+  return 0;
+}
+
+int ha_end_key_cache(KEY_CACHE_VAR *key_cache)
 {
-  (void) resize_key_cache(&dflt_keycache,(ulong) keybuff_size);
+  if (key_cache->cache)
+  {
+    end_key_cache(&key_cache->cache, 1);
+    return key_cache->cache ? 1 : 0;
+  }
+  return 0;
 }
 
 
diff --git a/sql/handler.h b/sql/handler.h
index 1c6e158180e..bbd5873b396 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -387,8 +387,9 @@ int ha_create_table(const char *name, HA_CREATE_INFO *create_info,
 		    bool update_create_info);
 int ha_delete_table(enum db_type db_type, const char *path);
 void ha_drop_database(char* path);
-void ha_key_cache(void);
-void ha_resize_key_cache(void);
+int ha_key_cache(KEY_CACHE_VAR *key_cache);
+int ha_resize_key_cache(KEY_CACHE_VAR *key_cache);
+int ha_end_key_cache(KEY_CACHE_VAR *key_cache);
 int ha_start_stmt(THD *thd); 
 int ha_report_binlog_offset_and_commit(THD *thd, char *log_file_name,
 				       my_off_t end_offset);
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index c9ec7e06f5b..cdffa8c7f50 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -386,6 +386,10 @@ bool reload_acl_and_cache(THD *thd, ulong options, TABLE_LIST *tables,
 void table_cache_init(void);
 void table_cache_free(void);
 uint cached_tables(void);
+void assign_cache_init(void);
+void assign_cache_free(void);
+void reassign_key_cache(KEY_CACHE_ASMT *key_cache_asmt,
+                        KEY_CACHE_VAR *new_key_cache);
 void kill_mysql(void);
 void close_connection(THD *thd, uint errcode, bool lock);
 bool check_access(THD *thd, ulong access, const char *db=0, ulong *save_priv=0,
@@ -407,6 +411,8 @@ int mysql_optimize_table(THD* thd, TABLE_LIST* table_list,
 			 HA_CHECK_OPT* check_opt);
 int mysql_assign_to_keycache(THD* thd, TABLE_LIST* table_list);
 int mysql_preload_keys(THD* thd, TABLE_LIST* table_list);
+int reassign_keycache_tables(THD* thd, KEY_CACHE_VAR* src_cache, 
+                             char *dest_name, bool remove_fl);
 
 bool check_simple_select();
 
@@ -769,7 +775,7 @@ extern pthread_mutex_t LOCK_mysql_create_db,LOCK_Acl,LOCK_open,
        LOCK_error_log, LOCK_delayed_insert,
        LOCK_delayed_status, LOCK_delayed_create, LOCK_crypt, LOCK_timezone,
        LOCK_slave_list, LOCK_active_mi, LOCK_manager,
-       LOCK_global_system_variables;
+       LOCK_global_system_variables, LOCK_assign;
 extern rw_lock_t	LOCK_grant;
 extern pthread_cond_t COND_refresh, COND_thread_count, COND_manager;
 extern pthread_attr_t connection_attrib;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 52b1b26d7a9..09203d127c7 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -289,8 +289,6 @@ ulong rpl_recovery_rank=0;
 ulong my_bind_addr;			/* the address we bind to */
 volatile ulong cached_thread_count= 0;
 
-ulonglong keybuff_size;
-
 double log_10[32];			/* 10 potences */
 
 time_t start_time;
@@ -343,6 +341,7 @@ pthread_mutex_t LOCK_mysql_create_db, LOCK_Acl, LOCK_open, LOCK_thread_count,
 		LOCK_delayed_insert, LOCK_delayed_status, LOCK_delayed_create,
 		LOCK_crypt, LOCK_bytes_sent, LOCK_bytes_received,
 	        LOCK_global_system_variables,
+                LOCK_assign,
 		LOCK_user_conn, LOCK_slave_list, LOCK_active_mi;
 rw_lock_t	LOCK_grant;
 pthread_cond_t COND_refresh,COND_thread_count, COND_slave_stopped,
@@ -866,6 +865,7 @@ void clean_up(bool print_message)
   grant_free();
   query_cache_destroy();
   table_cache_free();
+  assign_cache_free();
   hostname_cache_free();
   item_user_lock_free();
   lex_free();				/* Free some memory */
@@ -875,7 +875,8 @@ void clean_up(bool print_message)
     udf_free();
 #endif
   (void) ha_panic(HA_PANIC_CLOSE);	/* close all tables and logs */
-  end_key_cache(&dflt_keycache,1);
+  process_key_caches(&ha_end_key_cache);
+  ha_end_key_cache(&dflt_key_cache_var);
   delete_elements(&key_caches, free_key_cache);
   end_thr_alarm(1);			/* Free allocated memory */
 #ifdef USE_RAID
@@ -950,6 +951,7 @@ static void clean_up_mutexes()
 #endif
   (void) pthread_mutex_destroy(&LOCK_active_mi);
   (void) pthread_mutex_destroy(&LOCK_global_system_variables);
+  (void) pthread_mutex_destroy(&LOCK_assign);
   (void) pthread_cond_destroy(&COND_thread_count);
   (void) pthread_cond_destroy(&COND_refresh);
   (void) pthread_cond_destroy(&COND_thread_cache);
@@ -1508,14 +1510,15 @@ or misconfigured. This error can also be caused by malfunctioning hardware.\n",
 We will try our best to scrape up some info that will hopefully help diagnose\n\
 the problem, but since we have already crashed, something is definitely wrong\n\
 and this may fail.\n\n");
-  fprintf(stderr, "key_buffer_size=%lu\n", (ulong) keybuff_size);
+  fprintf(stderr, "key_buffer_size=%lu\n", 
+          (ulong) dflt_key_cache_var.buff_size);
   fprintf(stderr, "read_buffer_size=%ld\n", global_system_variables.read_buff_size);
   fprintf(stderr, "max_used_connections=%ld\n", max_used_connections);
   fprintf(stderr, "max_connections=%ld\n", max_connections);
   fprintf(stderr, "threads_connected=%d\n", thread_count);
   fprintf(stderr, "It is possible that mysqld could use up to \n\
 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = %ld K\n\
-bytes of memory\n", ((ulong) keybuff_size +
+bytes of memory\n", ((ulong) dflt_key_cache_var.buff_size +
 		     (global_system_variables.read_buff_size +
 		      global_system_variables.sortbuff_size) *
 		     max_connections)/ 1024);
@@ -2081,6 +2084,7 @@ static int init_thread_environment()
   (void) pthread_mutex_init(&LOCK_user_conn, MY_MUTEX_INIT_FAST);
   (void) pthread_mutex_init(&LOCK_active_mi, MY_MUTEX_INIT_FAST);
   (void) pthread_mutex_init(&LOCK_global_system_variables, MY_MUTEX_INIT_FAST);
+  (void) pthread_mutex_init(&LOCK_assign, MY_MUTEX_INIT_FAST);
   (void) my_rwlock_init(&LOCK_grant, NULL);
   (void) pthread_cond_init(&COND_thread_count,NULL);
   (void) pthread_cond_init(&COND_refresh,NULL);
@@ -2131,6 +2135,7 @@ static void init_ssl()
 static int init_server_components()
 {
   table_cache_init();
+  assign_cache_init();
   hostname_cache_init();
   query_cache_result_size_limit(query_cache_limit);
   query_cache_set_min_res_unit(query_cache_min_res_unit);
@@ -2193,7 +2198,10 @@ static int init_server_components()
   }
   if (opt_myisam_log)
     (void) mi_log(1);
-  ha_key_cache();
+
+  ha_key_cache(&dflt_key_cache_var);
+  process_key_caches(&ha_key_cache);
+  
 
 #if defined(HAVE_MLOCKALL) && defined(MCL_CURRENT)
   if (locked_in_memory && !geteuid())
@@ -3426,7 +3434,7 @@ enum options
   OPT_FLUSH_TIME, OPT_FT_MIN_WORD_LEN,
   OPT_FT_MAX_WORD_LEN, OPT_FT_MAX_WORD_LEN_FOR_SORT, OPT_FT_STOPWORD_FILE,
   OPT_INTERACTIVE_TIMEOUT, OPT_JOIN_BUFF_SIZE,
-  OPT_KEY_BUFFER_SIZE, OPT_LONG_QUERY_TIME,
+  OPT_KEY_BUFFER_SIZE, OPT_KEY_CACHE_BLOCK_SIZE, OPT_LONG_QUERY_TIME,
   OPT_LOWER_CASE_TABLE_NAMES, OPT_MAX_ALLOWED_PACKET,
   OPT_MAX_BINLOG_CACHE_SIZE, OPT_MAX_BINLOG_SIZE,
   OPT_MAX_CONNECTIONS, OPT_MAX_CONNECT_ERRORS,
@@ -4106,10 +4114,16 @@ replicating a LOAD DATA INFILE command.",
    IO_SIZE, 0},
   {"key_buffer_size", OPT_KEY_BUFFER_SIZE,
    "The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common.",
-   (gptr*) &keybuff_size, (gptr*) &keybuff_size, 0,
+   (gptr*) &dflt_key_cache_var.buff_size,
+   (gptr*) &dflt_key_cache_var.buff_size, 0,
    (enum get_opt_var_type) (GET_ULL | GET_ASK_ADDR),
    REQUIRED_ARG, KEY_CACHE_SIZE, MALLOC_OVERHEAD, (long) ~0, MALLOC_OVERHEAD,
    IO_SIZE, 0},
+  {"key_cache_block_size", OPT_KEY_CACHE_BLOCK_SIZE,
+   "The default size of key cache blocks",
+   (gptr*) &dflt_key_cache_var.block_size,
+   (gptr*) &dflt_key_cache_var.block_size, 0, GET_ULONG,
+   REQUIRED_ARG, KEY_CACHE_BLOCK_SIZE , 512, 1024*16, MALLOC_OVERHEAD, 512, 0},
   {"long_query_time", OPT_LONG_QUERY_TIME,
    "Log all queries that have taken more than long_query_time seconds to execute to file.",
    (gptr*) &global_system_variables.long_query_time,
@@ -4470,13 +4484,19 @@ struct show_var_st status_vars[]= {
   {"Handler_rollback",         (char*) &ha_rollback_count,      SHOW_LONG},
   {"Handler_update",           (char*) &ha_update_count,        SHOW_LONG},
   {"Handler_write",            (char*) &ha_write_count,         SHOW_LONG},
-  {"Key_blocks_used",          (char*) &my_blocks_used,        SHOW_LONG_CONST},
-  {"Key_read_requests",        (char*) &my_cache_r_requests,   SHOW_LONG},
-  {"Key_reads",                (char*) &my_cache_read,         SHOW_LONG},
-  {"Key_write_requests",       (char*) &my_cache_w_requests,   SHOW_LONG},
-  {"Key_writes",               (char*) &my_cache_write,        SHOW_LONG},
+  {"Key_blocks_used",          (char*) &dflt_key_cache_var.blocks_used,
+                                                          SHOW_LONG_CONST},
+  {"Key_read_requests",        (char*) &dflt_key_cache_var.cache_r_requests,
+                                                                SHOW_LONG},
+  {"Key_reads",                (char*) &dflt_key_cache_var.cache_read,
+                                                                SHOW_LONG},
+  {"Key_write_requests",       (char*) &dflt_key_cache_var.cache_w_requests,
+                                                                SHOW_LONG},
+  {"Key_writes",               (char*) &dflt_key_cache_var.cache_write,
+                                                                SHOW_LONG},
   {"Max_used_connections",     (char*) &max_used_connections,  SHOW_LONG},
-  {"Not_flushed_key_blocks",   (char*) &my_blocks_changed,     SHOW_LONG_CONST},
+  {"Not_flushed_key_blocks",   (char*) &dflt_key_cache_var.blocks_changed,
+                                                          SHOW_LONG_CONST},
   {"Not_flushed_delayed_rows", (char*) &delayed_rows_in_use,    SHOW_LONG_CONST},
   {"Open_tables",              (char*) 0,                       SHOW_OPENTABLES},
   {"Open_files",               (char*) &my_file_opened,         SHOW_LONG_CONST},
@@ -4682,7 +4702,8 @@ static void mysql_init_variables(void)
   threads.empty();
   thread_cache.empty();
   key_caches.empty();
-  if (!get_or_create_key_cache("default", 7))
+  if (!get_or_create_key_cache(DEFAULT_KEY_CACHE_NAME,
+                               strlen(DEFAULT_KEY_CACHE_NAME)))
     exit(1);
 
   /* Initialize structures that is used when processing options */
@@ -5320,18 +5341,20 @@ extern "C" gptr *
 mysql_getopt_value(const char *keyname, uint key_length,
 		   const struct my_option *option)
 {
-  if (!key_length)
-  {
-    keyname= "default";
-    key_length= 7;
-  }
   switch (option->id) {
   case OPT_KEY_BUFFER_SIZE:
   {
-    KEY_CACHE *key_cache;
+    KEY_CACHE_VAR *key_cache;
+    if (!(key_cache= get_or_create_key_cache(keyname, key_length)))
+      exit(1);
+    return (gptr*) &key_cache->buff_size;
+  }
+  case OPT_KEY_CACHE_BLOCK_SIZE:
+  {
+    KEY_CACHE_VAR *key_cache;
     if (!(key_cache= get_or_create_key_cache(keyname, key_length)))
       exit(1);
-    return (gptr*) &key_cache->size;
+    return (gptr*) &key_cache->block_size;
   }
   }
   return option->value;
@@ -5385,8 +5408,10 @@ static void get_options(int argc,char **argv)
   table_alias_charset= (lower_case_table_names ?
 			files_charset_info :
 			&my_charset_bin);
-  /* QQ To be deleted when we have key cache variables in a struct */
-  keybuff_size= (((KEY_CACHE *) find_named(&key_caches, "default", 7))->size);
+  /* Set up default values for a key cache */
+  KEY_CACHE_VAR *key_cache= &dflt_key_cache_var;
+  dflt_key_cache_block_size= key_cache->block_size;
+  dflt_key_buff_size= key_cache->buff_size;
 }
 
 
diff --git a/sql/set_var.cc b/sql/set_var.cc
index cb6c875d513..33dcd4b7e29 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -59,6 +59,9 @@
 #include "ha_innodb.h"
 #endif
 
+ulong dflt_key_buff_size;
+uint dflt_key_cache_block_size;
+
 static HASH system_variable_hash;
 const char *bool_type_names[]= { "OFF", "ON", NullS };
 TYPELIB bool_typelib=
@@ -88,7 +91,7 @@ static void fix_query_cache_size(THD *thd, enum_var_type type);
 static void fix_query_cache_min_res_unit(THD *thd, enum_var_type type);
 static void fix_myisam_max_extra_sort_file_size(THD *thd, enum_var_type type);
 static void fix_myisam_max_sort_file_size(THD *thd, enum_var_type type);
-static KEY_CACHE *create_key_cache(const char *name, uint length);
+static KEY_CACHE_VAR *create_key_cache(const char *name, uint length);
 void fix_sql_mode_var(THD *thd, enum_var_type type);
 static byte *get_error_count(THD *thd);
 static byte *get_warning_count(THD *thd);
@@ -137,6 +140,7 @@ sys_var_thd_ulong	sys_interactive_timeout("interactive_timeout",
 sys_var_thd_ulong	sys_join_buffer_size("join_buffer_size",
 					     &SV::join_buff_size);
 sys_var_key_buffer_size	sys_key_buffer_size("key_buffer_size");
+sys_var_key_cache_block_size  sys_key_cache_block_size("key_cache_block_size");
 sys_var_bool_ptr	sys_local_infile("local_infile",
 					 &opt_local_infile);
 sys_var_thd_bool	sys_log_warnings("log_warnings", &SV::log_warnings);
@@ -390,6 +394,7 @@ sys_var *sys_variables[]=
   &sys_interactive_timeout,
   &sys_join_buffer_size,
   &sys_key_buffer_size,
+  &sys_key_cache_block_size,
   &sys_last_insert_id,
   &sys_local_infile,
   &sys_log_binlog,
@@ -547,6 +552,8 @@ struct show_var_st init_vars[]= {
   {sys_interactive_timeout.name,(char*) &sys_interactive_timeout,   SHOW_SYS},
   {sys_join_buffer_size.name,   (char*) &sys_join_buffer_size,	    SHOW_SYS},
   {sys_key_buffer_size.name,	(char*) &sys_key_buffer_size,	    SHOW_SYS},
+  {sys_key_cache_block_size.name,   (char*) &sys_key_cache_block_size,
+                                                         	    SHOW_SYS},
   {"language",                language,                             SHOW_CHAR},
   {"large_files_support",     (char*) &opt_large_files,             SHOW_BOOL},	
   {sys_local_infile.name,     (char*) &sys_local_infile,	    SHOW_SYS},
@@ -1412,70 +1419,93 @@ void sys_var_collation_connection::set_default(THD *thd, enum_var_type type)
 }
 
 
+static LEX_STRING default_key_cache_base= {(char *) DEFAULT_KEY_CACHE_NAME, 7};
+
+static KEY_CACHE_VAR zero_key_cache= { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
+
+static KEY_CACHE_VAR *get_key_cache(LEX_STRING *cache_name)
+{
+  if (!cache_name || !cache_name->str || !cache_name->length ||
+       cache_name->str == default_key_cache_base.str ||
+      (cache_name->length == default_key_cache_base.length &&
+       !memcmp(cache_name->str, default_key_cache_base.str,
+               default_key_cache_base.length)))
+    cache_name= &default_key_cache_base;
+  return ((KEY_CACHE_VAR*) find_named(&key_caches,
+                                       cache_name->str, cache_name->length));
+}
+
+byte *sys_var_key_cache_param::value_ptr(THD *thd, enum_var_type type,
+					 LEX_STRING *base)
+{
+  KEY_CACHE_VAR *key_cache= get_key_cache(base);
+  if (!key_cache)
+    key_cache= &zero_key_cache;
+  return (byte*) key_cache + offset ;
+}
+  
 bool sys_var_key_buffer_size::update(THD *thd, set_var *var)
 {
+  int rc;
   ulonglong tmp= var->value->val_int();
+
   if (!base_name.length)
-  {
-    base_name.str= (char*) "default";
-    base_name.length= 7;
-  }
-  KEY_CACHE *key_cache= (KEY_CACHE*) find_named(&key_caches, base_name.str,
-						base_name.length);
+    base_name= default_key_cache_base;
+  KEY_CACHE_VAR *key_cache= get_key_cache(&base_name);
+                            
   if (!key_cache)
   {
     if (!tmp)					// Tried to delete cache
       return 0;					// Ok, nothing to do
+      
     if (!(key_cache= create_key_cache(base_name.str,
 				      base_name.length)))
       return 1;
   }
   if (!tmp)
   {
+    if (!key_cache->cache)
+      return 0;
     /* Delete not default key caches */
-    if (base_name.length != 7 || memcpy(base_name.str, "default", 7))
+    if (key_cache != &dflt_key_cache_var)
     {
       /*
-	QQ: Here we should move tables using this key cache to default
-	key cache
+         Move tables using this key cache to the default key cache
+         and remove this key cache if no tables are assigned to it
       */
-      delete key_cache;
-      return 0;
+      return (reassign_keycache_tables(thd, key_cache,
+                                       default_key_cache_base.str, 1));
     }
+    return 0;
   }
 
-  key_cache->size= (ulonglong) getopt_ull_limit_value(tmp, option_limits);
+  key_cache->buff_size= (ulonglong) getopt_ull_limit_value(tmp, option_limits);
 
-  /* QQ:  Needs to be updated when we have multiple key caches */
-  keybuff_size= key_cache->size;
-  ha_resize_key_cache();
-  return 0;
+  if (!key_cache->cache)
+    return (bool)(ha_key_cache(key_cache));
+  else
+    return (bool)(ha_resize_key_cache(key_cache));
 }
 
-static ulonglong zero=0;
-
-byte *sys_var_key_buffer_size::value_ptr(THD *thd, enum_var_type type,
-					 LEX_STRING *base)
+bool sys_var_key_cache_block_size::update(THD *thd, set_var *var)
 {
-  const char *name;
-  uint length;
+  ulong tmp= var->value->val_int();
 
-  if (!base->str)
-  {
-    name= "default";
-    length= 7;
-  }
-  else
-  {
-    name=   base->str;
-    length= base->length;
-  }
-  KEY_CACHE *key_cache= (KEY_CACHE*) find_named(&key_caches, name, length);
-  if (!key_cache)
-    return (byte*) &zero;
-  return (byte*) &key_cache->size;
+  if (!base_name.length)
+    base_name= default_key_cache_base;
+  KEY_CACHE_VAR *key_cache= get_key_cache(&base_name);
+                            
+  if (!key_cache && !(key_cache= create_key_cache(base_name.str,
+				                  base_name.length)))
+    return 1;
+ 
+  key_cache->block_size= (ulong) getopt_ull_limit_value(tmp, option_limits);
+
+  if (key_cache->cache)
+    /* Do not build a new key cache here */ 
+    return (bool) (ha_resize_key_cache(key_cache));
+  return 0;
 }
-  
 
 
 /*****************************************************************************
@@ -2062,27 +2092,40 @@ void delete_elements(I_List<NAMED_LIST> *list, void (*free_element)(gptr))
 
 /* Key cache functions */
 
-static KEY_CACHE *create_key_cache(const char *name, uint length)
+static KEY_CACHE_VAR *create_key_cache(const char *name, uint length)
 {
-  KEY_CACHE *key_cache;
+  KEY_CACHE_VAR *key_cache;
   DBUG_PRINT("info",("Creating key cache: %s", name));
-  if ((key_cache= (KEY_CACHE*) my_malloc(sizeof(KEY_CACHE),
-					 MYF(MY_ZEROFILL | MY_WME))))
+  if (length != default_key_cache_base.length ||
+      memcmp(name, default_key_cache_base.str, length))
   {
-    if (!new NAMED_LIST(&key_caches, name, length, (gptr) key_cache))
+    if ((key_cache= (KEY_CACHE_VAR*) my_malloc(sizeof(KEY_CACHE_VAR),
+					       MYF(MY_ZEROFILL | MY_WME))))
     {
-      my_free((char*) key_cache, MYF(0));
-      key_cache= 0;
+      if (!new NAMED_LIST(&key_caches, name, length, (gptr) key_cache))
+      {
+        my_free((char*) key_cache, MYF(0));
+        key_cache= 0;
+      }
     }
   }
+  else
+  {
+    key_cache= &dflt_key_cache_var;
+    if (!new NAMED_LIST(&key_caches, name, length, (gptr) key_cache))
+      key_cache= 0;
+  }
+     
   return key_cache;
 }
 
 
-KEY_CACHE *get_or_create_key_cache(const char *name, uint length)
+KEY_CACHE_VAR *get_or_create_key_cache(const char *name, uint length)
 {
-  KEY_CACHE *key_cache= (KEY_CACHE*) find_named(&key_caches, name,
-						length);
+  LEX_STRING key_cache_name;
+  key_cache_name.str= (char *) name;
+  key_cache_name.length= length;
+  KEY_CACHE_VAR *key_cache= get_key_cache(&key_cache_name);
   if (!key_cache)
     key_cache= create_key_cache(name, length);
   return key_cache;
@@ -2091,7 +2134,22 @@ KEY_CACHE *get_or_create_key_cache(const char *name, uint length)
 
 void free_key_cache(gptr key_cache)
 {
-  my_free(key_cache, MYF(0));
+  if (key_cache != (gptr) &dflt_key_cache_var)
+    my_free(key_cache, MYF(0));
+}
+
+bool process_key_caches(int (* func) (KEY_CACHE_VAR *))
+{
+
+  I_List_iterator<NAMED_LIST> it(key_caches);
+  NAMED_LIST *element;
+  while ((element= it++))
+  {
+    KEY_CACHE_VAR *key_cache= (KEY_CACHE_VAR *) element->data;
+    if (key_cache != &dflt_key_cache_var)
+      func(key_cache);     
+  }
+  return 0;
 }
 
 
diff --git a/sql/set_var.h b/sql/set_var.h
index 978aba3384a..f057041f30d 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -29,6 +29,8 @@ class sys_var;
 class set_var;
 typedef struct system_variables SV;
 extern TYPELIB bool_typelib, delay_key_write_typelib, sql_mode_typelib;
+extern uint dflt_key_cache_block_size;
+extern ulong dflt_key_buff_size;
 
 enum enum_var_type
 {
@@ -529,15 +531,43 @@ public:
 };
 
 
-class sys_var_key_buffer_size :public sys_var
+class sys_var_key_cache_param :public sys_var
 {
+protected:
+  uint offset;
 public:
-  sys_var_key_buffer_size(const char *name_arg)
+  sys_var_key_cache_param(const char *name_arg)
     :sys_var(name_arg)
-  {}
+  {
+    offset= 0;
+  }
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+};
+
+class sys_var_key_buffer_size :public sys_var_key_cache_param
+{
+public:
+  sys_var_key_buffer_size(const char *name_arg)
+    :sys_var_key_cache_param(name_arg)
+  {
+    offset= offsetof(KEY_CACHE_VAR, buff_size);
+  }
   bool update(THD *thd, set_var *var);
   SHOW_TYPE type() { return SHOW_LONGLONG; }
-  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+  bool check_default(enum_var_type type) { return 1; }
+  bool is_struct() { return 1; }
+};
+
+class sys_var_key_cache_block_size :public sys_var_key_cache_param
+{
+public:
+  sys_var_key_cache_block_size(const char *name_arg)
+    :sys_var_key_cache_param(name_arg)
+  {
+    offset= offsetof(KEY_CACHE_VAR, block_size);
+  }
+  bool update(THD *thd, set_var *var);
+  SHOW_TYPE type() { return SHOW_LONG; }
   bool check_default(enum_var_type type) { return 1; }
   bool is_struct() { return 1; }
 };
@@ -709,5 +739,6 @@ gptr find_named(I_List<NAMED_LIST> *list, const char *name, uint length);
 void delete_elements(I_List<NAMED_LIST> *list, void (*free_element)(gptr));
 
 /* key_cache functions */
-KEY_CACHE *get_or_create_key_cache(const char *name, uint length);
+KEY_CACHE_VAR *get_or_create_key_cache(const char *name, uint length);
 void free_key_cache(gptr key_cache);
+bool process_key_caches(int (* func) (KEY_CACHE_VAR *));
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 641ab46e5e0..c51dc65ac11 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -30,6 +30,7 @@
 
 TABLE *unused_tables;				/* Used by mysql_test */
 HASH open_cache;				/* Used by mysql_test */
+HASH assign_cache;
 
 static int open_unireg_entry(THD *thd,TABLE *entry,const char *db,
 			     const char *name, const char *alias);
@@ -53,7 +54,6 @@ void table_cache_init(void)
   mysql_rm_tmp_tables();
 }
 
-
 void table_cache_free(void)
 {
   DBUG_ENTER("table_cache_free");
@@ -63,7 +63,6 @@ void table_cache_free(void)
   DBUG_VOID_RETURN;
 }
 
-
 uint cached_tables(void)
 {
   return open_cache.records;
@@ -765,6 +764,8 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
   reg1	TABLE *table;
   char	key[MAX_DBKEY_LENGTH];
   uint	key_length;
+  KEY_CACHE_ASMT *key_cache_asmt;
+  KEY_CACHE_VAR *key_cache;
   DBUG_ENTER("open_table");
 
   /* find a unused table in the open table cache */
@@ -804,6 +805,77 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
     my_printf_error(ER_TABLE_NOT_LOCKED,ER(ER_TABLE_NOT_LOCKED),MYF(0),alias);
     DBUG_RETURN(0);
   }
+
+  VOID(pthread_mutex_lock(&LOCK_assign));
+  key_cache_asmt= (KEY_CACHE_ASMT*) hash_search(&assign_cache,
+                                              (byte*) key, key_length) ;
+  if (thd->open_options & HA_OPEN_TO_ASSIGN)
+  { 
+    /* When executing a CACHE INDEX command*/   
+    if (key_cache_asmt)
+    {
+      if (key_cache_asmt->requests++)
+      { 
+        /* Another thread are assigning this table to some key cache*/
+
+        /* Put the assignment request into the queue of such requests */
+        struct st_my_thread_var *last;
+        struct st_my_thread_var *thread= thd->mysys_var;        
+        if (! (last= key_cache_asmt->queue))
+          thread->next= thread;
+        else
+        {
+          thread->next= last->next;
+          last->next= thread;
+        }
+        key_cache_asmt->queue= thread;
+        
+        /* Wait until the request can be processed */
+        do
+        {
+          VOID(pthread_cond_wait(&thread->suspend, &LOCK_assign));
+        }
+        while (thread->next);
+      }
+    }
+    else
+    {
+      /* 
+         The table has not been explicitly assigned to any key cache yet;
+         by default it's assigned to the default key cache;
+      */
+      
+      if (!(key_cache_asmt=
+              (KEY_CACHE_ASMT *) my_malloc(sizeof(*key_cache_asmt),
+					   MYF(MY_WME | MY_ZEROFILL)))      ||
+          !(key_cache_asmt->db_name= my_strdup(db, MYF(MY_WME)))            ||
+	  !(key_cache_asmt->table_name= my_strdup(table_name, MYF(MY_WME))) ||
+          !(key_cache_asmt->table_key= my_memdup((const byte *) key,
+						 key_length, MYF(MY_WME))))
+      {
+        VOID(pthread_mutex_unlock(&LOCK_assign));
+        
+        if (key_cache_asmt)
+        {
+          if (key_cache_asmt->db_name)
+            my_free((gptr) key_cache_asmt->db_name, MYF(0));
+          if (key_cache_asmt->table_name)
+            my_free((gptr) key_cache_asmt->table_name, MYF(0));
+          my_free((gptr) key_cache_asmt, MYF(0));
+        }
+        DBUG_RETURN(NULL);
+      }
+      key_cache_asmt->key_length= key_length;
+      key_cache_asmt->key_cache= &dflt_key_cache_var;  
+      VOID(hash_insert(&assign_cache, (byte *) key_cache_asmt));
+      key_cache_asmt->requests++;     
+    }
+    key_cache_asmt->to_reassign= 0;
+  }
+
+  key_cache= key_cache_asmt ? key_cache_asmt->key_cache : &dflt_key_cache_var;  
+  VOID(pthread_mutex_unlock(&LOCK_assign));
+
   VOID(pthread_mutex_lock(&LOCK_open));
 
   if (!thd->open_tables)
@@ -846,6 +918,9 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
     }
     table->prev->next=table->next;		/* Remove from unused list */
     table->next->prev=table->prev;
+
+    table->key_cache= key_cache;
+    table->key_cache_asmt= key_cache_asmt;
   }
   else
   {
@@ -859,6 +934,8 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
       VOID(pthread_mutex_unlock(&LOCK_open));
       DBUG_RETURN(NULL);
     }
+    table->key_cache= key_cache;
+    table->key_cache_asmt= key_cache_asmt;
     if (open_unireg_entry(thd, table,db,table_name,alias) ||
 	!(table->table_cache_key=memdup_root(&table->mem_root,(char*) key,
 					     key_length)))
@@ -877,6 +954,8 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
 
   table->in_use=thd;
   check_unused();
+
+       
   VOID(pthread_mutex_unlock(&LOCK_open));
   if (refresh)
   {
@@ -1646,6 +1725,54 @@ bool rm_temporary_table(enum db_type base, char *path)
   DBUG_RETURN(error);
 }
 
+static void free_assign_entry(KEY_CACHE_ASMT *key_cache_asmt)
+{
+  DBUG_ENTER("free_assign_entry");
+  my_free((gptr) key_cache_asmt->table_key, MYF(0));
+  my_free((gptr) key_cache_asmt, MYF(0));
+  DBUG_VOID_RETURN;
+}
+
+static byte *assign_cache_key(const byte *record,uint *length,
+				 my_bool not_used __attribute__((unused)))
+{
+  KEY_CACHE_ASMT *entry=(KEY_CACHE_ASMT *) record;
+  *length=entry->key_length;
+  return (byte*) entry->table_key;
+}
+
+void assign_cache_init(void)
+{
+  VOID(hash_init(&assign_cache, &my_charset_bin,
+		 table_cache_size+16, 0, 0, assign_cache_key,
+		 (hash_free_key) free_assign_entry,0));
+}
+
+void assign_cache_free(void)
+{
+  DBUG_ENTER("assign_cache_free");
+  hash_free(&assign_cache);
+  DBUG_VOID_RETURN;
+}
+
+void reassign_key_cache(KEY_CACHE_ASMT *key_cache_asmt,
+                        KEY_CACHE_VAR *new_key_cache)
+{
+  if (key_cache_asmt->prev)
+  { 
+    /* Unlink key_cache_asmt from the assignment list for the old key cache */
+    if ((*key_cache_asmt->prev= key_cache_asmt->next))
+      key_cache_asmt->next->prev= key_cache_asmt->prev;
+  }
+  /* Link key_cache_asmt into the assignment list for the new key cache */
+  key_cache_asmt->prev= &new_key_cache->assign_list;
+  if ((key_cache_asmt->next= new_key_cache->assign_list))
+    key_cache_asmt->next->prev= &key_cache_asmt->next;
+  new_key_cache->assign_list= key_cache_asmt;
+
+  key_cache_asmt->key_cache= new_key_cache;
+}
+
 
 /*****************************************************************************
 ** find field in list or tables. if field is unqualifed and unique,
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 28208e4220f..0a873375471 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -1577,10 +1577,79 @@ int mysql_assign_to_keycache(THD* thd, TABLE_LIST* tables)
 {
   DBUG_ENTER("mysql_assign_to_keycache");
   DBUG_RETURN(mysql_admin_table(thd, tables, 0,
-				"assign_to_keycache", TL_WRITE, 0, 0, 0,
+				"assign_to_keycache", TL_READ, 0, 
+                                HA_OPEN_TO_ASSIGN, 0,
 				&handler::assign_to_keycache));
 }
 
+
+/*
+  Reassign all tables assigned to a key cache to another key cache
+
+  SYNOPSIS
+    reassign_keycache_tables()
+    thd	        Thread object
+    src_cache   Reference to the key cache to clean up
+    dest_name   Name of the cache to assign tables to
+    remove_fl   Flag to destroy key cache when all tables are reassigned      
+
+  RETURN VALUES
+    0	  ok
+   -1	  error
+*/
+
+int reassign_keycache_tables(THD* thd, KEY_CACHE_VAR* src_cache, 
+                             char *dest_name, bool remove_fl)
+{
+  int rc= 0;
+  TABLE_LIST table;
+  KEY_CACHE_ASMT *key_cache_asmt;
+
+  DBUG_ENTER("reassign_keycache_tables");
+
+  VOID(pthread_mutex_lock(&LOCK_assign));
+  for (key_cache_asmt= src_cache->assign_list ; 
+       key_cache_asmt;
+       key_cache_asmt= key_cache_asmt->next)
+    key_cache_asmt->to_reassign = 1;
+  key_cache_asmt= src_cache->assign_list; 
+  while (key_cache_asmt)
+  {
+    if (key_cache_asmt->to_reassign)
+    {
+      VOID(pthread_mutex_unlock(&LOCK_assign));
+      bzero((byte *) &table, sizeof(table));
+      table.option= dest_name;
+      table.lock_type= TL_READ;
+      table.db= key_cache_asmt->db_name;
+      table.alias= table.real_name= key_cache_asmt->table_name;
+      thd->open_options|= HA_OPEN_TO_ASSIGN;
+      table.table = open_ltable(thd, &table, TL_READ);
+      thd->open_options&= ~HA_OPEN_TO_ASSIGN;
+      table.table->pos_in_table_list= &table;
+      key_cache_asmt->triggered= 1;
+      rc= table.table->file->assign_to_keycache(thd, 0);
+      close_thread_tables(thd);
+      if (rc)
+        DBUG_RETURN(rc);
+      VOID(pthread_mutex_lock(&LOCK_assign));
+      key_cache_asmt= src_cache->assign_list;
+      continue;
+    }
+    else
+      key_cache_asmt= key_cache_asmt->next;
+  }
+  if (remove_fl && !src_cache->assign_list && src_cache != &dflt_key_cache_var)
+  {
+    end_key_cache(&src_cache->cache, 1);
+    src_cache->buff_size= 0;
+    src_cache->block_size= 0;
+  }
+  VOID(pthread_mutex_unlock(&LOCK_assign));
+  DBUG_RETURN(0);            
+}
+
+
 /*
   Preload specified indexes for a table into key cache
 
diff --git a/sql/sql_test.cc b/sql/sql_test.cc
index d2f97640010..baf11290489 100644
--- a/sql/sql_test.cc
+++ b/sql/sql_test.cc
@@ -321,8 +321,9 @@ w_requests: %10lu\n\
 writes:     %10lu\n\
 r_requests: %10lu\n\
 reads:      %10lu\n",
-	 my_blocks_used,my_blocks_changed,my_cache_w_requests,
-	 my_cache_write,my_cache_r_requests,my_cache_read);
+	 dflt_key_cache_var.blocks_used,dflt_key_cache_var.blocks_changed,
+         dflt_key_cache_var.cache_w_requests,dflt_key_cache_var.cache_write,
+         dflt_key_cache_var.cache_r_requests,dflt_key_cache_var.cache_read);
   pthread_mutex_unlock(&THR_LOCK_keycache);
 
   if (thd)
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 3997ed1bd82..e475fa1d052 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1872,15 +1872,26 @@ keycache_list:
         | keycache_list ',' assign_to_keycache;
 
 assign_to_keycache:
-        table_ident cache_keys_spec ident
+        table_ident cache_keys_spec IN_SYM ident
         {
           LEX *lex=Lex;
           SELECT_LEX *sel= &lex->select_lex;
           if (!sel->add_table_to_list(lex->thd, $1, NULL, 0,
-                                      TL_WRITE,
+                                      TL_READ,
                                       sel->get_use_index(),
                                       (List<String> *)0,
-                                      &($3)))        
+                                      &($4)))        
+            YYABORT;
+        }
+        |
+        table_ident cache_keys_spec IN_SYM DEFAULT
+        {
+          LEX *lex=Lex;
+          SELECT_LEX *sel= &lex->select_lex;
+          if (!sel->add_table_to_list(lex->thd, $1, NULL, 0,
+                                      TL_READ,
+                                      sel->get_use_index(),
+                                      (List<String> *)0))        
             YYABORT;
         }
         ;
diff --git a/sql/table.h b/sql/table.h
index 3132e72fb2f..ad5908ae5c2 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -55,6 +55,31 @@ typedef struct st_filesort_info
   ha_rows   found_records;      /* How many records in sort                  */
 } FILESORT_INFO;
 
+
+/* Table key cache assignment descriptor */
+/*
+   In future the similar structure is to be used for
+   an assignment of an index to a key cache: the index name will be added.
+   The name of the database catalog will be added as well.
+   The descriptors for the current assignments are put in the
+   assignment cache: assign_cache. If a table is not found in the cache
+   it is considered assigned to the default key cache.
+*/
+typedef struct st_key_cache_asmt
+{
+  char *db_name;                        /* db the table belongs to            */ 
+  char *table_name;                     /* the name of the table              */
+  char *table_key;                      /* key for the assignment cache       */
+  uint key_length;	                /* the length of this key             */
+  struct st_key_cache_var *key_cache;   /* reference to the key cache         */
+  struct st_key_cache_asmt **prev;      /* links in the chain all assignments */ 
+  struct st_key_cache_asmt *next;       /* to this cache                      */
+  struct st_my_thread_var *queue;       /* queue of requests for assignment   */
+  uint requests;                        /* number of current requests         */
+  bool to_reassign;                     /* marked when reassigning all cache  */
+  bool triggered;                       /* marked when assignment is triggered*/
+} KEY_CACHE_ASMT;
+
 /* Table cache entry struct */
 
 class Field_timestamp;
@@ -62,11 +87,13 @@ class Field_blob;
 
 struct st_table {
   handler *file;
-  Field **field;			/* Pointer to fields */
+  KEY_CACHE_VAR *key_cache;      /* Ref to the key cache the table assigned to*/
+  KEY_CACHE_ASMT *key_cache_asmt;/* Only when opened for key cache assignment */ 
+  Field **field;			/* Pointer to fields                  */
   Field_blob **blob_field;		/* Pointer to blob fields */
   HASH	name_hash;			/* hash of field names */
   byte *record[2];			/* Pointer to records */
-  byte *default_values;                 /* record with default values for INSERT */
+  byte *default_values;          /* Record with default values for INSERT     */
   byte *insert_values;                  /* used by INSERT ... UPDATE */
   uint fields;				/* field count */
   uint reclength;			/* Recordlength */
@@ -159,6 +186,7 @@ typedef struct st_table_list
 {
   struct	st_table_list *next;
   char		*db, *alias, *real_name;
+  char          *option;                /* Used by cache index  */ 
   Item		*on_expr;		/* Used with outer join */
   struct st_table_list *natural_join;	/* natural join on this table*/
   /* ... join ... USE INDEX ... IGNORE INDEX */
@@ -197,3 +225,5 @@ typedef struct st_open_table_list
   char	*db,*table;
   uint32 in_use,locked;
 } OPEN_TABLE_LIST;
+
+