The April 2012 InnoDB labs release introduces a new feature in InnoDB that allows you to choose the location of specific tables. For example, you can place critical tables onto an SSD drive while leaving the system tablespace on a hard drive. Conversely, you can store you primary database files on an SSD and put a seldom used but very large archive or reference table on a larger cheaper hard drive.
Innodb now makes use of the following existing syntax in MySQL ;
CREATE TABLE . . . DATA DIRECTORY = ‘absolute path of data directory’;
CREATE TABLE . . . PARTITION . . . DATA DIRECTORY = ‘absolute path of data directory’;
This syntax is used in MyISAM and Archive engines to make use of symbolic links in those operating systems that support it. But InnoDB can use this syntax on any OS since it stores the path in a new system table called SYS_DATAFILES. There is also …[Read more]