Thursday, May 14, 2009

Partitioning Presentation from MySQL Conference

It appears that my presentation isn’t available at the conference site. I’ve added it below.   Sorry for the delay. 

Without some context that I talked about at the conference the presentation may not make sense.

Partitioning creates a grain in the table.  Select queries that go with that grain can be quicker, at times much faster, but select queries that go against that grain can be slower, at times much slower. 

An example is a table that is partitioned by date and has an orderId primary key.  Queries that select data by date will either be almost as fast (partitioning can add a slight overhead) as the non-partitioned table to much faster.   But queries that don’t query by date will have to query all the partitions.  A table partitioned 12 times, one partition for each month, results in 12 index partitions.  As the indexes are partitioned by date, a query by the primary key of orderId means all twelve index partitions must be queried to look for the order in question.  Querying twelve index partitions is  going to be slower than just one unpartitioned table. 

Having said that, there are cases where partitioning dramatically improves performance, such as optimizing a table partition by partition. 

But now I’m starting to cover too much of what is in the presentation.  So here it is.

No comments: