Unanswered: The equivalent of 'tablespace' in MySQL?
Well in Oracle, there's something called tablespace; basically it's a logical container where the database objects (table, index, etc) reside. In Oracle, when you create a database object (for example, index), you should specify which tablespace should contain the object. If you don't specify anything, then the object will be created in USER tablespace.
The implication of this is that you can put database objects in different storages. Why? Because when you create tablespace, you must also specify the datafile where the tablespace physically resides.
For example, you have two different partitions, /dev/sda2 and /dev/sdb1. You have a table named MY_PETS, and the table has an index named MY_PETS_PK_IDX. To ensure optimal performance, you want to store the table in /dev/sda2 (mounted as /data, for example), and the index in /sdb1 (mounted as /idx).
In Oracle, you achieve it by the use of tablespace. For above case, you should create two tablespaces, let's say TS_PETS and TS_PETS_IDX. The first tablespace is to contain the table, while the second is for the index. Of course, the tablespace TS_PETS should be located in /dev/sda2, while the tablespace TS_PETS_IDX should be located in /dev/sdb1. (1) Thus, when creating the table MY_PETS, you should specify its tablespace (TS_PETS). The tablespace TS_PETS resides on /dev/sda2 (mount point: /data), and so will the table be. (2) And when creating the index MY_PETS_PX_IDX, you should specify its tablespace as well (TS_PETS_IDX). This tablespace resides on /dev/sdb1 (mount point: /idx), and the index will be on /dev/sdb1 as well.
Thus, the goal is achieved, that the table and the index resides in different partitions.
That's how it works in Oracle, how about MySQL? I heard MySQL 5.1 has 'create tablespace' option, but isn't MySQL 5.1x is still in beta?
Nonetheless, I'm using MySQL 5.0 instead of 5.1 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:
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?