Performance on having different file for tables in MySQL.

Question

In MySQL, we have an option to have separate file for tables. I just wanted to know if using this option would decrease performance,for example in querying tables.

Answer 1

The benchmarks I've seen for InnoDB specifically within MySQL show a moderate performance decrease for write (INSERT) operations, but more favorable read operations, particularly as the number and size of the tables increases.

Your mileage may vary depending on many other factors, however. Database buffer size, indexing in the file system, etc.

If your tables aren't huge, per-table files probably won't make much of a difference either way, as the majority of your database will be in memory.

Answer 2

This is really going to depend on the size of the tables, the amount of memory you're allowing MySQL to use, and possibly the speed of the storage (disk).

But it will make negligible difference.

Other things will have much more effect on speed: table design, index coverage, memory, storage speed, etc. You'll get much, much more payoff, performance-wise, by addressing those issues.