What's the advantage of splitting a mysql table into smaller ones without physical distribution?

Question

I've seen such design in my company.

A big mysql table is split into tens of smaller ones of identical schema on the same server in the same db, without any physical distribution, no innodb_file_per_table, no partitioning, etc. The data is distributed evenly and the read/write access pattern is uniform across all tables.

What's advantage of doing so performance-wise?

Answer 1

If your reads/writes are distributed evenly, I think there's no performance advantage. If you were write heavy and writing to, for example, just 2-3 of the tables - then you could benefit (see slides 28-34 of this presentation).

A few ideas about operational benefits:

• It'll be easier to scale out/shard once there's too much data or too much traffic.
• Depending on how your backups are made, it might be quicker to recover something if you can determine in which of the tables lost data was located.

Edit: one more thought: if the queries are executed in parallel, there might be performance benefits - you can get more IOPS from your RAID, and better localisation of multi-core system if the working set fits in the memory.