Tuesday, June 06, 2006

Table partitioning in MySQL 5.1

I'm very excited that MySQL implements this feature in version 5.1.x, all the new features are bridging the gap between open source and propietary RDMS like Oracle, this feature particularly makes MySQL suitable for Data warehouse systems, not just OLTP systems as it's well known.

There are a lot of benefits in the use of table partitioning like:

- Eases the administration of the tables
- Reduces time taken in large amount of DML
- Improves the response of some queries

Table partitioning allows the DBA to define how the table and the data cointained could be stored phisically, while remaining the logical structure the same. For example a table with data of 5 years (theorically a large table) could be splitted (depending on the needs) in 5 partitions, reducing the time taken by some queries that may fit into a particular year, easing the administration of the table when the data from a year needs to be dropped or moved, enhancing the response for bulk DMLs in different years, etc.

There are some features not available yet like partitioning by range on a date column (http://bugs.mysql.com/bug.php?id=17540) and one that i reported, i couldn't find how to get data from a specific partition rather than knowing wich partition a query accesses ( http://bugs.mysql.com/bug.php?id=20279), but i think is just a matter of time.


Ved said...

nice article

sap upgrade tools said...

I am not at all aware of this feature that it has been incorporated in this version. I am feeling so excited like you to know about it. Yes this feature do have several benefits and will ease DBA with the proper handling of tables.