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

actually - if your reads/writes are distributed evenly - i think there's no performance advantage. if you were write heavy and writing to let's say just 2-3 of the tables - then you could benefit [see slides 28-34 of this presentation].

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 i/ops from your raid, better localisation of multi-core system if the working set fits in the memory.