Yes, but isn't MySQL 5.1x is still in beta?
I wonder whether MySQL 5.0 (I'm using 5.0.7) is capable of doing such thing; that is, putting particular table in a particular 'tablespace', which in turn is put in a particular hard drive partition.
IIRC, the MyISAM storage engine uses the file
table_name.MYD for a table, and
table_name.MYI for an index. So each table has two files: one for the table's content itself, while another is for its index. Thus, if I have a table name pets, then I will have the files pets.MYD and pets.MYI.
However, I wonder if we can specify the path of the MyISAM files. For instance, I have the following partitions: /dev/sda2 (mounted as /data1), /dev/sdb1 (mounted as index1), /dev/sdb2 (mounted as data2), and /dev/sdc1 (mounted as index2). I also have two tables: 'orders' and 'customers'. I want to have the following storage scheme:
(1) orders.MYD should be stored in /dev/sda2 (/data1)
(2) orders.MYI should be stored in /dev/sdb1 (/index1)
(3) customers.MYD should be stored in /dev/sdb2 (/data2)
(4) customers.MYI should be stored in /dev/sdc1 (index2)
Is it possible with MyISAM? And if that's the case, how? How to specify the path of a particular MyISAM file?
I'm also thinking about InnoDB. In InnoDB, there is a way to specify the path of InnoDB datafiles; using
innodb_data_file_path option in my.cnf option files.
However, I wonder whether there is a way to associate an InnoDB file with a particular table. For example, if I specify the following line:
[mysqld]
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend
Then I will have two InnoDB data files: ibdata1 and ibdata2.
However, if I have two tables: 'costumers' and 'orders', is it possible to put the table costumers in ibdata1, and put orders in ibdata2? Is it possible to specify a specific InnoDB data file for a particular table? And how?