MySQL equivalent of tablespace?
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, /sda2 and /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 /sda2, and the index in /sdb1.
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 /sda2, while the tablespace TS_PETS_IDX should be located in /sdb1.
(1) When creating the table MY_PETS, you should specify its tablespace, which is TS_PETS. The tablespace TS_PETS resides on /sda2, and so will the table be.
(2) And when creating the index MY_PETS_PX_IDX, you should specify its tablespace, which is TS_PETS_IDX. This tablespace resides on /sdb1, and the index will be on /sdb1 as well.
Thus, the goal is achieved, that the table and the index resides in different paritions.
That's how it works in Oracle; how about MySQL?
Thanks,
-Kresh