Take the 2-minute tour ×
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.

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.

share|improve this question
4  
A 100,000 row table growing at 100,000 rows per year is a piece of cake to query and manage for any mature RDBMS, MySQL included. Go with one table, index it properly, and consider it a solved problem. If you're planning on storing billions of rows, then we should talk about partitioning. –  Nick Chammas Mar 7 '12 at 22:58
1  
That sounds...horrifying. –  Ben Brocka Mar 7 '12 at 22:58
add comment

6 Answers

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
1  
If I could upvote +3, I would. –  ypercube Mar 8 '12 at 8:08
add comment

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 http://dev.mysql.com/doc/refman/5.1/en/partitioning.html 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.

EDIT

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
    
Good answer, and I completely agree with the edit too! If it isn't broke, don't fix it! You should consider the long term usage of the db, and will the entire app get replaced before the data size gets too unwieldy. Or, you could just TELL the devs that they need to fix their code to work with a new db schema - creating a single table called tableName, populating it with all the data, and then dropping the old tables (in your DEV environment only), is a really easy task, and locating this table name in the code and fixing it should also be relatively easy! –  Dave Rix Mar 7 '12 at 21:27
1  
@Dave, while I agree with that ain't broke sentiment, to clarify: I would consider it broke if it will be causing catastrophic downtime or firefighting in the near future. I guess what I was getting at is avoid what might be premature micro-optimizations for your use case just because you read on some blog the best practice is something else. –  atxdba Mar 8 '12 at 4:33
add comment

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:

datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
uri VARCHAR(1024),
host VARCHAR(255),
user_agent VARCHAR(255),
etc...

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

weblogs.20120301
weblogs.20120302
weblogs.20120303

etc.

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
add comment

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:

http://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

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.