Wednesday, October 21, 2009

MySQL Partitioning

One of the recent project I were assigned, I had to handle lots of event records and subscriber details(which is in Telco environment). So we looked at the database partitioning for subscriber profile by the number ranges and prepaid/post-paid way. For the event tables, it would be a weekly basis tables. If we create the tables structures according to the above logic and going to handle at the coding level, then the SQL queries get very complex. Then I found the a very effective way of handling the database partitioning at the database layer itself without bothering about what kind of tables which we are having at the coding level.

MySQL 5.1 provides a very stable feature called partitioning. It provides both Horizontal and Vertical partitioning.

Check this article: Improving Database Performance with Partitioning
This specified the performance improvement and how easy for a developer to work with the higher data load.

For Oracle DB partitioning: Oracle Partitioning Oracle9i Enterprise Edition

Even though the partitioning is a very rich feature, there are few limitations as well. If you are using with innodb please check the limitations before you use it, most importantly it does not support foreign keys.
Check this: Restrictions and Limitations on Partitioning

No comments: