Description:
It's a good idea to have hot data always cached in the memory. Many RDBMS implements an ability to assign a dedicate buffer pool to certain tables. Since MySQL 5.5.4 has a separate innodb buffer pool to avoid lock contention, I guess it's not so difficult to implement a separate/dedicated/named buffer pool to optimize performance more. Might be such a dedicate buffer pool may be divided to several ones to avoid lock contentions further.
Yes. We have a similar functionality for MyISAM tables like CACHE INDEX ... INTO. However, we don't have one for InnoDB.
How to repeat:
n/a
Suggested fix:
We need an additional command like below:
mysql> CREATE BUFFERPOOL buf_master_1 SIZE=256M SHARDS=4 ENGINE=InnoDB;
* devided to 4 parts
mysql> CACHE TABLE t1 INTO buf_master_1;