Partitioning in MySQL 5.1

Sheeri Cabral of the Pythian Group (http://www.pythian.com) presented a session on the new partitioning feature in MySQL 5.1 at the January 2009 Boston User Group.

From her description:
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

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!

Category:

Science & Technology

Tags:

• partitioning
• mysql
• sheeri cabral
• sheeri
• pythian
• partition
• performance
• user group
• boston user group

[supprof1]

thank you

[gfuggiano]

Nice presentation.

[creuwb]

This is so great presentation! The pythian group have the most valuable professionals those works with database systems and are the biggest reference of MySQL.

Congratulations!