Many files:

  New feature: preload indexes into key cache.
mi_preload.c:
  new file
Many files:
  Added preload statement.

15 files changed, 186 insertions, 8 deletions

diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc
index 213f5baf388..d9b9f3130de 100644
--- a/sql/ha_myisam.cc
+++ b/sql/ha_myisam.cc
@@ -675,6 +675,72 @@ int ha_myisam::repair(THD *thd, MI_CHECK &param, bool optimize)
 
 
 /*
+  Preload pages of the index file for a table into the key cache.
+*/
+
+int ha_myisam::preload_keys(THD* thd, HA_CHECK_OPT *check_opt)
+{
+  int error;
+  const char *errmsg;
+  ulonglong map= ~(ulonglong) 0;
+  TABLE_LIST *table_list= table->pos_in_table_list;
+  my_bool ignore_leaves= table_list->ignore_leaves;
+
+  DBUG_ENTER("ha_myisam::preload_keys");
+
+  /* Check validity of the index references */ 
+  if (table_list->use_index)
+  {
+    key_map kmap= get_key_map_from_key_list(table, table_list->use_index);
+    if (kmap == ~(key_map) 0)
+    {
+      errmsg= thd->net.last_error;
+      error= HA_ADMIN_FAILED;
+      goto err;
+    }
+    if (kmap)
+      map= kmap;
+  }
+  
+  mi_extra(file, HA_EXTRA_PRELOAD_BUFFER_SIZE,
+           (void *) &thd->variables.preload_buff_size);
+
+  if ((error= mi_preload(file, map, ignore_leaves)))
+  {
+    switch (error) {
+    case HA_ERR_NON_UNIQUE_BLOCK_SIZE:
+      errmsg= "Indexes use different block sizes";
+      break;
+    case HA_ERR_OUT_OF_MEM:
+      errmsg= "Failed to allocate buffer";
+      break;
+    default: 
+      char buf[ERRMSGSIZE+20];
+      my_snprintf(buf, ERRMSGSIZE, 
+                  "Failed to read from index file (errno: %d)", my_errno);
+      errmsg= buf;
+    }
+    error= HA_ADMIN_FAILED;
+    goto err;
+  }
+  
+  DBUG_RETURN(HA_ADMIN_OK);
+
+ err:
+  {
+    MI_CHECK param;
+    myisamchk_init(&param);
+    param.thd= thd;
+    param.op_name= (char*)"preload_keys";
+    param.db_name= table->table_cache_key;
+    param.table_name= table->table_name;
+    param.testflag= 0;
+    mi_check_print_error(&param, errmsg);
+    DBUG_RETURN(error);
+  }
+}
+
+/*
   Deactive all not unique index that can be recreated fast
 
   SYNOPSIS
diff --git a/sql/ha_myisam.h b/sql/ha_myisam.h
index 154429ecc0d..8486e25556b 100644
--- a/sql/ha_myisam.h
+++ b/sql/ha_myisam.h
@@ -127,6 +127,7 @@ class ha_myisam: public handler
   int optimize(THD* thd, HA_CHECK_OPT* check_opt);
   int restore(THD* thd, HA_CHECK_OPT* check_opt);
   int backup(THD* thd, HA_CHECK_OPT* check_opt);
+  int preload_keys(THD* thd, HA_CHECK_OPT* check_opt);
 #ifdef HAVE_REPLICATION
   int dump(THD* thd, int fd);
   int net_read_dump(NET* net);
diff --git a/sql/handler.cc b/sql/handler.cc
index f2b7dbdf531..e84c9bf3569 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -588,6 +588,11 @@ int handler::analyze(THD* thd, HA_CHECK_OPT* check_opt)
   return HA_ADMIN_NOT_IMPLEMENTED;
 }
 
+int handler::preload_keys(THD* thd, HA_CHECK_OPT* check_opt)
+{
+  return HA_ADMIN_NOT_IMPLEMENTED;
+}
+
 /*
   Read first row (only) from a table
   This is never called for InnoDB or BDB tables, as these table types
diff --git a/sql/handler.h b/sql/handler.h
index 8c23a3625e0..a2408ab0fe0 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -304,6 +304,7 @@ public:
   virtual int optimize(THD* thd,HA_CHECK_OPT* check_opt);
   virtual int analyze(THD* thd, HA_CHECK_OPT* check_opt);
   virtual int backup(THD* thd, HA_CHECK_OPT* check_opt);
+  virtual int preload_keys(THD* thd, HA_CHECK_OPT* check_opt);
   /*
     restore assumes .frm file must exist, and that generate_table() has been
     called; It will just copy the data file and run repair.
diff --git a/sql/lex.h b/sql/lex.h
index f85431aadf7..0cbc048e00c 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -221,6 +221,7 @@ static SYMBOL symbols[] = {
   { "KILL",		SYM(KILL_SYM),0,0},
   { "LAST",		SYM(LAST_SYM),0,0},
   { "LEADING",		SYM(LEADING),0,0},
+  { "LEAVES",		SYM(LEAVES),0,0},
   { "LEFT",		SYM(LEFT),0,0},
   { "LEVEL",		SYM(LEVEL_SYM),0,0},
   { "LIKE",		SYM(LIKE),0,0},
@@ -299,6 +300,7 @@ static SYMBOL symbols[] = {
   { "POLYGON",		SYM(POLYGON),0,0},
   { "PURGE",		SYM(PURGE),0,0},
   { "PRECISION",	SYM(PRECISION),0,0},
+  { "PRELOAD",          SYM(PRELOAD),0,0},
   { "PREV",		SYM(PREV_SYM),0,0},
   { "PRIMARY",		SYM(PRIMARY_SYM),0,0},
   { "PROCEDURE",	SYM(PROCEDURE),0,0},
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index ea6e544a1fd..e0c3f5aeaec 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -246,6 +246,7 @@ void debug_sync_point(const char* lock_name, uint lock_timeout);
 /* Options to add_table_to_list() */
 #define TL_OPTION_UPDATING	1
 #define TL_OPTION_FORCE_INDEX	2
+#define TL_OPTION_IGNORE_LEAVES 4
 
 /* Some portable defines */
 
@@ -393,6 +394,8 @@ int mysql_analyze_table(THD* thd, TABLE_LIST* table_list,
 			HA_CHECK_OPT* check_opt);
 int mysql_optimize_table(THD* thd, TABLE_LIST* table_list,
 			 HA_CHECK_OPT* check_opt);
+int mysql_preload_keys(THD* thd, TABLE_LIST* table_list);
+
 bool check_simple_select();
 
 SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length);
@@ -584,6 +587,8 @@ enum find_item_error_report_type {REPORT_ALL_ERRORS, REPORT_EXCEPT_NOT_FOUND,
 extern const Item **not_found_item;
 Item ** find_item_in_list(Item *item, List<Item> &items, uint *counter,
 			  find_item_error_report_type report_error);
+key_map get_key_map_from_key_list(TABLE *table, 
+				  List<String> *index_list);
 bool insert_fields(THD *thd,TABLE_LIST *tables,
 		   const char *db_name, const char *table_name,
 		   List_iterator<Item> *it);
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index f73bd6721f0..7f3c69cb7c9 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -3464,6 +3464,7 @@ enum options
   OPT_NET_BUFFER_LENGTH, OPT_NET_RETRY_COUNT,
   OPT_NET_READ_TIMEOUT, OPT_NET_WRITE_TIMEOUT,
   OPT_OPEN_FILES_LIMIT,
+  OPT_PRELOAD_BUFFER_SIZE,
   OPT_QUERY_CACHE_LIMIT, OPT_QUERY_CACHE_MIN_RES_UNIT, OPT_QUERY_CACHE_SIZE,
   OPT_QUERY_CACHE_TYPE, OPT_RECORD_BUFFER,
   OPT_RECORD_RND_BUFFER, OPT_RELAY_LOG_SPACE_LIMIT, OPT_RELAY_LOG_PURGE,
@@ -4244,6 +4245,11 @@ Disable with --skip-isam",
    "If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of files.",
    (gptr*) &open_files_limit, (gptr*) &open_files_limit, 0, GET_ULONG,
    REQUIRED_ARG, 0, 0, 65535, 0, 1, 0},
+   {"preload_buffer_size", OPT_PRELOAD_BUFFER_SIZE,
+    "The size of the buffer that is allocated when preloading indexes",
+    (gptr*) &global_system_variables.preload_buff_size,
+    (gptr*) &max_system_variables.preload_buff_size, 0, GET_ULONG,
+    REQUIRED_ARG, 32*1024L, 1024, 1024*1024*1024L, 0, 1, 0},
 #ifdef HAVE_QUERY_CACHE
   {"query_cache_limit", OPT_QUERY_CACHE_LIMIT,
    "Don't cache results that are bigger than this.",
diff --git a/sql/set_var.cc b/sql/set_var.cc
index e4adbb0a318..ddaef4c4e14 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -199,6 +199,8 @@ sys_var_thd_ulong	sys_net_retry_count("net_retry_count",
 					    &SV::net_retry_count,
 					    fix_net_retry_count);
 sys_var_thd_bool	sys_new_mode("new", &SV::new_mode);
+sys_var_thd_ulong       sys_preload_buff_size("preload_buffer_size",
+                                              &SV::preload_buff_size);
 sys_var_thd_ulong	sys_read_buff_size("read_buffer_size",
 					   &SV::read_buff_size);
 sys_var_thd_ulong	sys_read_rnd_buff_size("read_rnd_buffer_size",
@@ -402,6 +404,7 @@ sys_var *sys_variables[]=
   &sys_net_wait_timeout,
   &sys_net_write_timeout,
   &sys_new_mode,
+  &sys_preload_buff_size,
   &sys_pseudo_thread_id,
   &sys_query_cache_size,
 #ifdef HAVE_QUERY_CACHE
@@ -573,6 +576,7 @@ struct show_var_st init_vars[]= {
   {"log_error",               (char*) log_error_file,               SHOW_CHAR},
   {"port",                    (char*) &mysql_port,                  SHOW_INT},
   {"protocol_version",        (char*) &protocol_version,            SHOW_INT},
+  {sys_preload_buff_size.name, (char*) &sys_preload_buff_size,      SHOW_SYS},
   {sys_pseudo_thread_id.name, (char*) &sys_pseudo_thread_id,        SHOW_SYS},
   {sys_read_buff_size.name,   (char*) &sys_read_buff_size,	    SHOW_SYS},
   {sys_read_rnd_buff_size.name,(char*) &sys_read_rnd_buff_size,	    SHOW_SYS},
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index fc95f5deb40..c7db77e1d84 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -35,8 +35,6 @@ static int open_unireg_entry(THD *thd,TABLE *entry,const char *db,
 			     const char *name, const char *alias);
 static void free_cache_entry(TABLE *entry);
 static void mysql_rm_tmp_tables(void);
-static key_map get_key_map_from_key_list(TABLE *table,
-					 List<String> *index_list);
 
 
 extern "C" byte *table_cache_key(const byte *record,uint *length,
@@ -2058,8 +2056,8 @@ bool setup_tables(TABLE_LIST *tables)
 }
 
 
-static key_map get_key_map_from_key_list(TABLE *table, 
-					 List<String> *index_list)
+key_map get_key_map_from_key_list(TABLE *table, 
+				  List<String> *index_list)
 {
   key_map map=0;
   List_iterator_fast<String> it(*index_list);
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 925afde2202..996331b071f 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -365,6 +365,7 @@ struct system_variables
   ulong net_retry_count;
   ulong net_wait_timeout;
   ulong net_write_timeout;
+  ulong preload_buff_size;
   ulong query_cache_type;
   ulong read_buff_size;
   ulong read_rnd_buff_size;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index f31b3305e07..04f784e62dc 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -59,7 +59,7 @@ enum enum_sql_command {
   SQLCOM_CHANGE_DB, SQLCOM_CREATE_DB, SQLCOM_DROP_DB, SQLCOM_ALTER_DB,
   SQLCOM_REPAIR, SQLCOM_REPLACE, SQLCOM_REPLACE_SELECT,
   SQLCOM_CREATE_FUNCTION, SQLCOM_DROP_FUNCTION,
-  SQLCOM_REVOKE,SQLCOM_OPTIMIZE, SQLCOM_CHECK,
+  SQLCOM_REVOKE,SQLCOM_OPTIMIZE, SQLCOM_CHECK, SQLCOM_PRELOAD_KEYS,
   SQLCOM_FLUSH, SQLCOM_KILL,  SQLCOM_ANALYZE,
   SQLCOM_ROLLBACK, SQLCOM_COMMIT, SQLCOM_SLAVE_START, SQLCOM_SLAVE_STOP,
   SQLCOM_BEGIN, SQLCOM_LOAD_MASTER_TABLE, SQLCOM_CHANGE_MASTER,
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 1d2449839d8..2596d349a1c 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1819,6 +1819,15 @@ mysql_execute_command(THD *thd)
     res = mysql_restore_table(thd, tables);
     break;
   }
+  case SQLCOM_PRELOAD_KEYS:
+  {
+    if (check_db_used(thd, tables) ||
+        check_access(thd, INDEX_ACL, tables->db, &tables->grant.privilege))
+      goto error; 
+    res = mysql_preload_keys(thd, tables);
+    break;
+  }
+      
 
 #ifndef EMBEDDED_LIBRARY
   case SQLCOM_CHANGE_MASTER:
@@ -3937,6 +3946,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
   ptr->lock_type=   lock_type;
   ptr->updating=    test(table_options & TL_OPTION_UPDATING);
   ptr->force_index= test(table_options & TL_OPTION_FORCE_INDEX);
+  ptr->ignore_leaves= test(table_options & TL_OPTION_IGNORE_LEAVES);
   ptr->derived=	    table->sel;
   if (use_index)
     ptr->use_index=(List<String> *) thd->memdup((gptr) use_index,
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 3cac88d05b2..a73f55077c9 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -1331,6 +1331,7 @@ static int mysql_admin_table(THD* thd, TABLE_LIST* tables,
 	goto err;
       continue;
     }
+    table->table->pos_in_table_list= table;
     if ((table->table->db_stat & HA_READ_ONLY) && open_for_modify)
     {
       char buff[FN_REFLEN + MYSQL_ERRMSG_SIZE];
@@ -1487,6 +1488,28 @@ int mysql_optimize_table(THD* thd, TABLE_LIST* tables, HA_CHECK_OPT* check_opt)
 
 
 /*
+  Preload specified indexes for a table into key cache
+
+  SYNOPSIS
+    mysql_preload_keys()
+    thd	        Thread object
+    tables      Table list (one table only)
+
+  RETURN VALUES
+    0	  ok
+   -1	  error
+*/
+
+int mysql_preload_keys(THD* thd, TABLE_LIST* tables)
+{
+  DBUG_ENTER("mysql_preload_keys");
+  DBUG_RETURN(mysql_admin_table(thd, tables, 0,
+				"preload_keys", TL_READ, 0, 0, 0,
+				&handler::preload_keys));
+}
+
+
+/*
   Create a table identical to the specified table
 
   SYNOPSIS
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index e3b2c738949..6c120574644 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -256,6 +256,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	KEY_SYM
 %token	LEADING
 %token	LEAST_SYM
+%token	LEAVES
 %token	LEVEL_SYM
 %token	LEX_HOSTNAME
 %token	LIKE
@@ -309,6 +310,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	DUMPFILE
 %token	PACK_KEYS_SYM
 %token	PARTIAL
+%token	PRELOAD
 %token	PRIMARY_SYM
 %token	PRIVILEGES
 %token	PROCESS
@@ -584,7 +586,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	type int_type real_type order_dir opt_field_spec lock_option
 	udf_type if_exists opt_local opt_table_options table_options
 	table_option opt_if_not_exists opt_no_write_to_binlog opt_var_type opt_var_ident_type
-	delete_option opt_temporary all_or_any opt_distinct
+	delete_option opt_temporary all_or_any opt_distinct opt_ignore_leafs
 
 %type <ulong_num>
 	ULONG_NUM raid_types merge_insert_types
@@ -613,7 +615,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	key_alg opt_btree_or_rtree
 
 %type <string_list>
-	key_usage_list
+	key_usage_list 
 
 %type <key_part>
 	key_part
@@ -661,10 +663,11 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %type <NONE>
 	query verb_clause create change select do drop insert replace insert2
 	insert_values update delete truncate rename
-	show describe load alter optimize flush
+	show describe load alter optimize preload flush
 	reset purge begin commit rollback slave master_def master_defs
 	repair restore backup analyze check start
 	field_list field_list_item field_spec kill column_def key_def
+	preload_list preload_keys
 	select_item_list select_item values_list no_braces
 	opt_limit_clause delete_limit_clause fields opt_values values
 	procedure_list procedure_list2 procedure_item
@@ -733,6 +736,7 @@ verb_clause:
 	| lock
 	| kill
 	| optimize
+	| preload
 	| purge
 	| rename
         | repair
@@ -1824,6 +1828,55 @@ table_to_table:
 	    YYABORT;
 	};
 
+preload:
+	PRELOAD
+	{
+	  LEX *lex=Lex;
+	  lex->sql_command=SQLCOM_PRELOAD_KEYS;
+	}
+	preload_list
+	{}
+	;
+
+preload_list:
+	preload_keys
+	| preload_list ',' preload_keys;
+
+preload_keys:
+	table_ident preload_keys_spec opt_ignore_leafs
+	{
+	  LEX *lex=Lex;
+	  SELECT_LEX *sel= &lex->select_lex;
+	  if (!sel->add_table_to_list(lex->thd, $1, NULL, $3, 
+                                      TL_READ, 
+                                      sel->get_use_index(),
+                                      (List<String> *)0))
+            YYABORT;
+	}
+	;
+
+preload_keys_spec:
+	keys_or_index { Select->select_lex()->interval_list.empty(); }
+	preload_key_list_or_empty 
+	{
+	  LEX *lex=Lex;
+	  SELECT_LEX *sel= &lex->select_lex;
+	  sel->use_index= sel->interval_list;
+	  sel->use_index_ptr= &sel->use_index;
+	}          
+	;
+
+preload_key_list_or_empty:
+	/* empty */
+	| '(' key_usage_list2 ')' {}
+	;
+
+opt_ignore_leafs:
+	/* empty */
+	{ $$= 0; }
+	| IGNORE_SYM LEAVES { $$= TL_OPTION_IGNORE_LEAVES; }
+	;
+
 /*
   Select : retrieve data from table
 */
@@ -4251,6 +4304,7 @@ keyword:
 	| INSERT_METHOD		{}
 	| RELAY_THREAD		{}
 	| LAST_SYM		{}
+	| LEAVES                {}
 	| LEVEL_SYM		{}
 	| LINESTRING		{}
 	| LOCAL_SYM		{}
@@ -4294,6 +4348,7 @@ keyword:
 	| PASSWORD		{}
 	| POINT_SYM		{}
 	| POLYGON		{}
+	| PRELOAD               {}
 	| PREV_SYM		{}
 	| PROCESS		{}
 	| PROCESSLIST_SYM	{}
diff --git a/sql/table.h b/sql/table.h
index 2aefe23cb2f..3132e72fb2f 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -181,6 +181,7 @@ typedef struct st_table_list
   bool		straight;		/* optimize with prev table */
   bool          updating;               /* for replicate-do/ignore table */
   bool		force_index;		/* Prefer index over table scan */
+  bool          ignore_leaves;          /* Preload only non-leaf nodes */
 } TABLE_LIST;
 
 typedef struct st_changed_table_list