Essay: Benefits of partitioning of database
Partitioning has two benefits (Chung, 2007): It enhances simplified data management. This is achieved since it allows a DBA to control data management inside the database. Through creation of partitions, DBA simplifies the performance of certain data operations. In a partitioned table for instance, the DBA can drop some partitions while retaining the others intact. MySQL automatically maintains partitions. In this case, DBA does not have to separate and maintain horizontal schemes of partitioning for a table. A historic kind of table that has data belonging to customers partitioned across some year ranges and has the automatically partitioned enforced by database server without calling for DBA’s intervention (Gros and Amsleg, 2005).
Increased performance is another advantage of partitioning. MySQL optimizer knows the partitions that have the data able to satisfy a particular query during scan operations thus scan them. A large table with for instance some one million partitions per row may be broken so that each partition has some hundred of thousand rows. Once a query is asked and it needs data from a single partition and if a scan of the table is necessary, only the say two hundred thousand rows will be accessed instead of the two million. Less time is thus taken to perform the query as MySQL samples only 100,000 instead of 1,000,000 rows (Eyas 2003). It is also possible to stripe a partitioned table across varied physical drives. This is possible through specification of directory paths for specific partitions which now allows physical I/O contention reduction when multiple partitions are accessed at the same time.