Article Preview

COLUMN

Deep Dive On Creating MySQL Tables Part 4

Partitioning Options and Definitions

Issue: 11.4 (July/August 2013)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a Sr. Data Modeler for a well-known national retailer. But in his 17 years of IT experience, he has been everything from a PC Technician to iSeries System Administrator to iSeries Programmer and Sr. Technical Lead. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 18,693
Starting Page Number: 71
Article Number: 11410
Related Link(s): None

Excerpt of article text...

In this article we are going to talk a little bit about what partitioning is and how to do it in the context of MySQL. This will then wrap up our deep dive into the MySQL CREATE TABLE statement. Try not to weep too much because that means that we will be able to move on to other exciting database topics!

My current employer is a nationwide retailer. One of the databases I have the pleasure of working on is the 25TB (terabytes) accounts payable system that is running on Oracle. The biggest table, the payable line items, is about 6TB. The table is currently partitioned by year, month and general ledger account. You may be tempted to think that no matter what query is run against this table it is going to be slow. The reality is that most queries run against this table not only perform well, but often times have sub-second response times. That is because of the miracle of partitioning.

Partitioning is basically the means by which a table may be spread across different physical areas of the file system. This has all kinds of interesting implications! In the case of the 6TB table above, you could put a rolling three-month history on SSD drives since this is where much of the day to day activity is happening. For the next twelve months you could put those on some good quality drives which would give you good performance for another huge chunk of work that the end users are doing. The remainder of the partitions could be on "average" drives that are accessed only occasionally, but still have to be on-line. If this was a non-partitioned table you would have to keep the entire table on a single drive or set of similar drives and performance would suffer, but thanks to the miracle of partitioning you can take the parts of the table that need the performance the most and place them on the faster drives and still manage to give relatively decent performance to the other parts of the table.

In the CREATE TABLE statement there are three sections that explicitly deal with partitioning: Partition Options, Partition Definition, and Subpartition Definition. We will begin by working through the Partitioning Options. At this point I need to caveat this like I have the other articles in this series. Partitioning is an extremely technical and complicated topic that requires a lot of work to truly master. The intent of this article is to supply a brief overview that will hopefully give you a feel for when you would use partitioning, some of the options you will have, and some of the constraints you will have to work within.

Partition Options

...End of Excerpt. Please purchase the magazine to read the full article.