Inspired by Baron's earlier post, here is one I hear quite frequently -
"If you enable innodb_file_per_table, each table is it's own .ibd file. You can then relocate the heavy hit tables to a different location and create symlinks to the original location."
There are a few things wrong with this advice:
- InnoDB does not support these symlinks. If you run an ALTER TABLE command, what you will find is that a new temporary table is created (in the original location!), the symlink is destroyed, and the temporary table is renamed. Your "optimization" is lost.
- Striping (with RAID) is usually a far better optimization. Striping a table across multiple disks effectively balances the 'heavy hit' access across many more disks. With 1 disk/table you are …