Re: Tablespace in MySQL
Hi.
At least as I know, InnoDB doesn't support real tablespace creation. Of course you are able to 'virtually create a tablespace using engine InnoDB' but this won't work at all. Is like creating foreign keys under Myisam, it doesn't give you any error, but it doesn't worked at all, cause Myisam is non-transactional and doesn't support this option, the same with InnoDB, it doesn't support tablespace. If you want to create a tablespace you need to use a Engine that support it in this case, Falcon and NDBCluster.
But Falcon is preferred in your case, since I don't think you have any cluster disk to use NDB.
Second, you can't divide index and datafiles since, Falcon engine and InnoDB also, saved data and index in a single file not separated like Myisam and Maria Engine, but remember that Myisam and Maria are non-transactional so you cannot include them in a tablespace or use foreign keys with them.
As I said Falcon and InnoDB don't separate data and indexes they save both things in single files, this is not a problem at all as you may can think off, first because Mysql Developers decide to made it this way, cause is faster and keep both data and index in memory for faster rendering.
You need to separate data and index in other's DB like Oracle, SQL Server, etc. But not in Mysql transactional engines, in other's DB you need to separate data and index in different Hard rive cause these DB read alternately 1 index, 1 data, 1 index, 1 data. So imagine if index and data will be in 1 single HD this obviously would cause the HD reading very stressful but in Falcon datafile it first read all indexes them recover all data, so for this reason you don't need to separate index and data in different HD, cause this issue is solved in Mysql.