I read somewhere that InnoDB can store all the tables in a single file or in separate files. Is this a configuration option? What is the default setting? A file for all tables or a file per table? What is the recommended setting?
The setting is innodb_file_per_table, and it's default 0 (ie. use one big file) up to MySQL 5.1, and then in either 5.5 or 5.6 the default changed. Changing this value will not affect tables which already exist.
Even with innodb_file_per_table set to ON you still need the shared tablespace file (ibdata1 file by default).
There is no one recommended setting, it depends on your databases. Some advantages:
What problem are you trying to solve?
So, it's always better to use InnoDB_file_per_table option and if are turning on this option in