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 enable partitioning, for version 5.5, compile with command line input “--with-partition” (the “--” is for command line input). To disable partitioning on a server, use command line input “--skip-partition”. If it is disabled you cannot use the any partitioned tables or create new partitions. To re-enable partitioning, after the skip command is issued, you have to restart the server.
Some of the main advantages of using partitioning with MySQL are:
1) A table can be spread across multiple partitioned disks
2) Can add and remove data from tables by adding and removing partition storage space
3) Can make query searches faster and more efficient by reorganizing the partitions in the table
4) Parallel queries on the separate partitions of the table
5) No single point of failure to slow down queries
Some disadvantages are:
1) Storage functions, storage procedures, UDFs or plug-ins, and declared or user variables are not allowed.
2) Can only use the symbols “+”, “-“, and “*” when the return value is an integer or NULL
3) “DIV” can be used but the symbol “/” is no allowed
4) The operational symbols: “|” for or, “&” for and, “^”, “<>”for bit shifting to the right cannot be used.