MySQL Partitioning

1191 Words3 Pages

MySQL Partitioning

Partitioning for MySQL is a support for: partitioned file systems and servers, and multiple systems and servers for a single database. MySQL partitioning is completed on the tables not the file systems or servers. Table partitioning allows a table to be stored over multiple storage areas. MySQL uses row partitioning known as “horizontal partitioning”. Of version 5.5, MySQL does not allow column partitioning known as “vertical partitioning”, and it is not in development to do so. “The MySQL partitioning engine” is supported by most of the MySQL compatible storage engines, yet a particular partition must be on the same storage engine for all instance of it. setting the storage engine is the same as is done for a non-partition table.

But how do you tell if your version of MySQL supports partitioning? There are two main ways to check if your version of MySQL has the capability of using partitioning. The first is to use the command “SHOW VARIABLES LIKE ‘%partition%’;” at the command line prompt “mysql>”. This displays the variables with partition in the name column of the table of variables. The response should be a table with the entry for name to be “have_partitioning” and the corresponding column with “YES”. The “YES” means that the version of MySQL supports partitioning. The second way is to use the command “SHOW PLUGINS;” at the command line prompt “mysql>”. This will display all the plug-ins that your version of MySQL has and uses. The only plug-in to be concerned with is the “partition” variable in the table. If its status is “ACTIVE”, then partitioning is enabled for the server. If there is no “partition” plug-in, then your version does not have partitioning capabilities.

How do you enable partitioning? To...

... middle of paper ...

...ons from the queries. Using pruning on a partition table can lead to speeds of “an order of magnitude” faster than the same table without partitioning. Pruning helps in cases of the “WHERE” statement which checks the entire table. The “IN” command is used as such “column IN (____, ___, …)”. Here checks only a set of the partition table that was created by the pruning command. It can also be used in conjunction with the WHERE, as in “WHERE _____ IN (___, ___, ___)”. The “BETWEEN” statement works in a similar way as the “IN” statement, as use for pruning. Pruning needs to: find the minimal partition column value, find the maximum partition column value, and check partitions from min to max inclusively.

Works Cited http://dev.mysql.com/doc/refman/5.5/en/partitioning-management.html; Last Accessed March 5, 2010: 11:30am; © 2010, Oracle Corporation and/or its affiliates

Open Document