Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


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.

share|improve this question
up vote 16 down vote

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.

share|improve this answer

You can use merge tables, however they're more antiquated from the 4.x versions. Given your application is manually partitioned as it is either a) you are running a really old version or b) the original developer wasn't aware of table partitions.

In short if you're running 5.1+ you can let mysql do this partitioning for you. See If you're using 5.5 you should check those specific docs as you will find some differences.

There are many advantages to partitioning. However it really depends on the dataset at hand, access patterns and how it is to be indexed. Also, keep in mind my following comments are in the context of mysql 5+ partitioning, NOT older mysql Merge tables; although they are sometimes discussed in terms of partitions.

Some examples:

  • Straight bucketing (or hashing) based on frequently accessed lookup key. If you're pretty much always looking up by a primary or other unique key then mysql can cut the search space by a factor of how ever many partitions you have. Note however this can be detrimential if you partition by one key and then frequently search by another key. If you search by a key the data is not partitioned by then it must do MORE searches on lookups (one for each partition, b/c frankly, it doesn't know where the data is)
  • Consider situations were you have a temporal set of records that grows by date and you periodically prune out the previous month. If you're partitioning by date then you can simply drop a partition which is just as fast as dropping a table, no matter how big. If you were to prune such a table by dates you'd have to issue one or more DELETE queries where each individual row is deleted. The downside to this is mysql doesn't automatically create new partitions once you've reached the max date you've accounted for in this scenario; you need extra maintenance scripts built on your part to add partitions as they are needed.
  • If you are using myisam checks and recoveries are much faster. Consider a 100G myisam table. If you wanted to recover a crashed table you would need about 100G of spare disk space at least. If it were partitioned into 10 different chunks of equal size then you only need 10G of space (and less key_sort_buffer memory for fast recovery); but would need to do an iteration for each partition.

So in summary, the general approach of partitioning tables can offer many benefits. However it's not a magic bullet to be applied blindly without consideration to access patterns and how exactly you are partitioning.

I could imagine situations where the desired partitioning is very application specific and would be better suited to have that logic sitting in the application layer. However given your straight modulus 10 description this does not seem like such a case.


In writing up my description I forgot that you stated your table is 100K rows. With out the full schema of your table and it's average row length it's hard to say for certain, but in general that sounds medium sized even for modest hardware. At the same time, if it's not causing problems the way it is now or in the foreseeable future then don't spend time and introduce risk by changing it.

share|improve this answer

What the previous developer has done for you is built their own implementation of partition-by-hash. MySQL literally supports this natively from MySQL 5.1:

I can not think of a good reason so implement your own partition-by-hash rather than rely on the native version[1]. Performing schema changes will be a nightmare.

I also rarely recommend partition-by-hash (the native implementation). I think it would be useful if you could use it to parallel search each of the partitions at once (which MySQL will not do). If you need to search across multiple partitions, the scheme you have described will often be much slower.

[1] However, for some of the other partitioning types it can make sense to roll your own partitioning. MySQL forces you to make your partition key part of your primary key and all unique indexes.

share|improve this answer

In response to the question:

is whether or not this is a viable solution

IMHO, This seems like unnecessary overhead. You could simply index and partition a single table properly unless there's some other information not revealed in the description.

In response to the question:

... if it's a good practice in any situation

IMHO, vertical sharding can make sense depending on the context. When I see this, it's typically in some sort of log form. Let's pretend we're using this for web server logs and we want to partition by month. Instead of altering an existing table in place every day, we could create a new table every day and log rows to that table.

e.g. Pretend a web log table might take the form:

uri VARCHAR(1024),
host VARCHAR(255),
user_agent VARCHAR(255),

Your solution creates tables in as needed in the weblog database:



This way, the data remains maintainable and searchable. Extraction becomes a normal periodic process. Continuous operations are not locked out by operations on older data.

In the scenario you've presented you're locked into a structure anyway, so why not use a single table optimized for this purpose? The algorithm based storage of rows seems sketchy and error prone.

share|improve this answer

If a query targets enormous data, split of data by query conditions would has a notable improvement of performance. But such split, as you have seen, brings some programming issues up.

So the question is: Is that split worth for performance, or it harm the performance?

If you have a transaction that needs to lock multiple rows over several tables and there are issues on it(for example, deadlock or transaction timeout), you may like to combine them into single table and rewrite the SQL to repair the issues.

When I think about whether to split table, I used to consider the trade-off between performance gaining and programming complexity.

In your situation, the modification of existing code may be a long-term solution to make code easier to maintain. I would suggest a try to meta-programming. For example, using StringTemplate to generate SQL dynamically. I like to generate SQL from meta-programming engine if modification of existing code is too hard.

share|improve this answer

When you need to store files in table, to use this metodology helps to export, repair and restore.

I have tables with >30 Gb partitioned in 10 tables. These tables have only ID - BLOB and to me is easily to keep. And I use MyISAM to save INNODB buffer.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.