Re: Can we have different tablespace for indexing in innodb storage engine?

[Rick James]

InnoDB works one of two ways:

file-per-table:
In the directory (named after the database):
.frm -- schema for the table
.ibd -- the "tablespace"
In ibdata1:
Other necessary stuff

not file-per-table:
In the directory (named after the database):
.frm -- schema for the table
In ibdata1:
This is a communal tablespace for all tables;
Other necessary stuff

A tablespace contains 16KB blocks (optionally 8KB for the 'plugin'). Data and the PRIMARY KEY live in some of the blocks; the secondary indexes live in other blocks. The blocks are organized in a BTree structure -- one per index.