Pythian Group

At the January 2009 Boston User Group I presented a session on the new partitioning feature in MySQL 5.1. I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.

The slides are available at http://www.technocation.org/files/doc/2009_01_Partitioning.pdf. The video is embedded at the bottom of this post, and is also available at youtube at http://www.youtube.com/watch?v=zvN9XI-FraI.

Notes:

The partitioning part of the MySQL Manual is at: http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html.

The functions that are not allowed in partitioning expressions are listed at: http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

We also had an interesting development — according to the manual, an INSERT to a partitioned table that includes values that do not have a partition should insert all values up to the failure point. The example I used (and that you can see in the video) is:

insert into nums_list_mod (id) VALUES (4001, 4004, 4002);

Where 4001 and 4002 fit into partitions but there is no partition defined that will accept 4004. According to the manual, the value 4001 should be inserted, but 4004 and 4002 should not. Instead, we got the behavior that nothing was inserted (sql_mode=” and this was not within a transaction).

Enjoy the presentation!