Mysql – Splitting Tables in MySQL. Good practice

best practicesdatabase-designMySQL

I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data.

The tables look like this:

[tableName_0]
[tableName_1]
[tableName_2]
[tableName_3]
[tableName_4]
[tableName_5]
[tableName_6]
[tableName_7]
[tableName_8]
[tableName_9]

The primary key is an integer id field. The application uses a hash algorithm (id mod 10) to know what table to access when doing lookups. For example id = 10 would result to [tableName_0].

Combined, the tables have probably 100,000 rows and the growth rate is relatively low.

So, my question is whether or not this is a viable solution or even if it's a good practice in any situation. My theory is to push to have them combined as it will make things easier as far as UNIONs, etc go. The main downside is changing all the application code and whether it is even worth it in the long run.

Best Answer

I think everyone is over-complicating this. The key point here is:

Combined, the tables have probably 100,000 rows and the growth rate is relatively low.

This is a piece of cake for any RDBMS to handle. Go with one table, index it properly, and consider it a solved problem.

You don't need to consider partitioning, whether "homemade" or otherwise, until you start handling extremely large volumes of data--think billions of rows and up.

Related Question