How to preload the Innodb table and index data into memory cache

when Mysql shutdown or crash, the innodb data will lost in memory cache,so the data query speed  will drop,How to preload the innodb table and index data into memory cache?


first, mysql provide a command,like myisam "load cache" command,but now the mysql doesn't provide it.


second,use "select ..." statement to load the mysql innodb table and index data when we restart the mysql.


if the mysql server includes many datas, you must preload by multi-process.


the SQL script as following:


#vi initfile.sql
SET SESSION group_concat_max_len=100*1024*1024;
SELECT GROUP_CONCAT(CONCAT('SELECT COUNT(`',column_name,'`) FROM `',table_schema,'`.`',table_name,'` FORCE INDEX (`',index_name,'`)') SEPARATOR ' UNION ALL ') INTO @sql FROM information_schema.statistics WHERE table_schema NOT IN ('information_schema','mysql', 'cr_debug') AND seq_in_index = 1;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET SESSION group_concat_max_len=@@group_concat_max_len;

add this file to my.cnf file.


[mysqld]
init-file=/home/oracle/mysql5.1.55/bin/initfile.sql