MySQL partitioning makes data distribution of individual tables (typically we recommend partition for large & complex I\/O table for performance, scalability and manageability<\/em>) across multiple files based on partition strategy \/ rules. In very simple terms, different portions of table are stored as separate tables in different location to distribute I\/O optimally. The user defined division of data by some rule is known as partition function<\/em>, In MySQL we partition data by RANGE<\/em><\/strong> of values \/ LIST<\/strong><\/em> of values \/ internal hashing function \/ linear hashing function. By restricting the query examination on the selected partitions by matching rows increases the query performance by multiple times compared to the same query on a non partitioned table, This methodology is also called partition pruning (trimming of unwanted partitions), Please find below example of partition pruning:<\/p>\n Write a SELECT query benefitting partition pruning:<\/p>\n What is explicit partitioning in MySQL and how is it different from partition pruning ?\u00a0<\/strong><\/p>\n In MySQL we can explicitly select partition and sub-partitions when executing a statement matching a given WHERE condition, This sounds very much similar to partition pruning, but there is a difference:<\/p>\n Explicit partition example:<\/p>\n Query explicitly mentioning partition:<\/p>\n RANGE partitioning For example, let us suppose that you wish to partition based on the year contract ended:<\/p>\n It is also possible to partition a table by RANGE<\/strong>, based on the value of a TIMESTAMP<\/strong> column, using the UNIX_TIMESTAMP()<\/strong> function, as shown in this example:<\/p>\n LIST partitioning HASH partitioning P.S. :\u00a0If you do not include a PARTITIONS clause, the number of partitions defaults to 1.<\/em><\/p>\n LINEAR HASH partitioning KEY partitioning P.S. –\u00a0if the unique key column were not defined as NOT NULL, then the previous statement would fail.<\/i><\/p>\n Subpartitioning It is also possible to define subpartitions explicitly using SUBPARTITION clauses to specify options for individual subpartitions:<\/p>\n Things to remember:<\/p>\n MySQL partitioning limitations A PRIMARY KEY must include all columns in the table’s partitioning function:<\/em><\/p>\n Expect this error after running above script –\u00a0ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function<\/strong><\/p>\n The right way of doing it:<\/p>\n Most popular limitation of MySQL<\/strong> – Primary key is by definition a unique key, this restriction also includes the table’s primary key, if it has one. The example below explains this limitation better:<\/em><\/p>\n Both of the above scripts will return this error –\u00a0ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function<\/strong><\/p>\n The right way of doing it:<\/p>\n In above example, the primary key does not include all columns referenced in the partitioning expression. However, both of the statements are valid !<\/p>\n You can still successfully partition a MySQL table without unique keys – this also includes having no primary key and\u00a0you may use any column or columns in the partitioning expression as long as the column type is compatible with the partitioning type, The example below shows partitioning a table with no unique \/ primary keys:<\/p>\n You cannot later add a unique key to a partitioned table unless the key includes all columns used by the table’s partitioning expression, The example below explains this much better:<\/p>\n However, the next statement fails, because column10 is part of the partitioning key, but is not part of the proposed primary key:<\/p>\n MySQL partitioning limitations (at storage engine level)<\/strong><\/p>\n InnoDB<\/em><\/p>\n NDB storage engine\u00a0<\/em><\/p>\n FEDERATED storage engine\u00a0<\/em><\/p>\n CSV storage engine<\/em><\/p>\n MERGE storage engine\u00a0<\/i><\/p>\n MySQL functions shown in the following list are allowed in partitioning expressions:<\/strong><\/p>\n MySQL partitioning and locks\u00a0<\/strong><\/p>\n Effect on DML statements<\/em><\/p>\n Effect on DML statements<\/em><\/p>\n Effect on other statements<\/em><\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" MySQL partitioning makes data distribution of individual tables (typically we recommend partition for large & complex I\/O table for performance, scalability and manageability) across multiple files based on partition strategy \/ rules. In very simple […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,140,15],"tags":[5,141,16],"yoast_head":"\nCREATE TABLE tab1 (\r\n col1 VARCHAR(30) NOT NULL,\r\n col2 VARCHAR(30) NOT NULL,\r\n col3 TINYINT UNSIGNED NOT NULL,\r\n col4 DATE NOT NULL\r\n)\r\nPARTITION BY RANGE( col3 ) (\r\n PARTITION p0 VALUES LESS THAN (100),\r\n PARTITION p1 VALUES LESS THAN (200),\r\n PARTITION p2 VALUES LESS THAN (300),\r\n PARTITION p3 VALUES LESS THAN MAXVALUE\r\n);<\/pre>\n
SELECT col1, col2, col3, col4\r\nFROM tab1\r\nWHERE col3 > 200 AND col3 < 250;<\/pre>\n
\n
CREATE TABLE customer (\r\n cust_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\r\n cust_fname VARCHAR(25) NOT NULL,\r\n cust_lname VARCHAR(25) NOT NULL,\r\n cust_phone INT NOT NULL,\r\n cust_fax INT NOT NULL\r\n) \r\n PARTITION BY RANGE(cust_id) (\r\n PARTITION p0 VALUES LESS THAN (100),\r\n PARTITION p1 VALUES LESS THAN (200),\r\n PARTITION p2 VALUES LESS THAN (300),\r\n PARTITION p3 VALUES LESS THAN MAXVALUE\r\n);\r\n<\/pre>\n
mysql> SELECT * FROM customer PARTITION (p1);<\/pre>\n
\n<\/strong>In RANGE partitioning you can partition values within a given range, Ranges should be contiguous but not overlapping, usually defined by VALUES LESS THAN operator, The following examples explain how to create and use RANGE partitioning for MySQL performance:<\/p>\nCREATE TABLE customer_contract(\r\n cust_id INT NOT NULL,\r\n cust_fname VARCHAR(30),\r\n cust_lname VARCHAR(30),\r\n st_dt DATE NOT NULL DEFAULT '1970-01-01',\r\n end_dt DATE NOT NULL DEFAULT '9999-12-31',\r\n contract_code INT NOT NULL,\r\n contract_id INT NOT NULL\r\n)\r\nPARTITION BY RANGE (contract_id) (\r\n PARTITION p0 VALUES LESS THAN (50),\r\n PARTITION p1 VALUES LESS THAN (100),\r\n PARTITION p2 VALUES LESS THAN (150),\r\n PARTITION p3 VALUES LESS THAN (200)\r\n);<\/pre>\n
CREATE TABLE customer_contract(\r\n cust_id INT NOT NULL,\r\n cust_fname VARCHAR(30),\r\n cust_lname VARCHAR(30),\r\n st_dt DATE NOT NULL DEFAULT '1970-01-01',\r\n end_dt DATE NOT NULL DEFAULT '9999-12-31',\r\n contract_code INT NOT NULL,\r\n contract_id INT NOT NULL\r\n)\r\nPARTITION BY RANGE (year(end_dt)) (\r\n PARTITION p0 VALUES LESS THAN (2001),\r\n PARTITION p1 VALUES LESS THAN (2002),\r\n PARTITION p2 VALUES LESS THAN (2003),\r\n PARTITION p3 VALUES LESS THAN (2004)\r\n);<\/pre>\n
CREATE TABLE sales_forecast (\r\n sales_forecast_id INT NOT NULL,\r\n sales_forecast_status VARCHAR(20) NOT NULL,\r\n sales_forecast_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\r\n)\r\nPARTITION BY RANGE ( UNIX_TIMESTAMP(sales_forecast_updated) ) (\r\n PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),\r\n PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),\r\n PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),\r\n PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),\r\n PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),\r\n PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),\r\n PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),\r\n PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),\r\n PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),\r\n PARTITION p9 VALUES LESS THAN (MAXVALUE)\r\n);<\/pre>\n
\n<\/strong>The difference between\u00a0RANGE\u00a0<\/em>and\u00a0LIST\u00a0<\/em>partitioning is: In LIST\u00a0<\/em>partitioning, each partition is grouped on the selected list of values of a specific column. You can do it by\u00a0PARTITION BY LIST (EXPR)<\/em>\u00a0<\/em>where EXPR<\/em> is the selected column for list partition, We have explained LIST\u00a0<\/em>partitioning with example below:<\/p>\nCREATE TABLE students (\r\n student_id INT NOT NULL,\r\n student_fname VARCHAR(30),\r\n student_lname VARCHAR(30),\r\n student_joined DATE NOT NULL DEFAULT '1970-01-01',\r\n student_separated DATE NOT NULL DEFAULT '9999-12-31',\r\n student_house INT,\r\n student_grade_id INT\r\n)\r\nPARTITION BY LIST(student_grade_id) (\r\n PARTITION P1 VALUES IN (1,2,3,4),\r\n PARTITION P2 VALUES IN (5,6,7),\r\n PARTITION P3 VALUES IN (8,9,10),\r\n PARTITION P4 VALUES IN (11,12)\r\n);<\/pre>\n
\n<\/strong>HASH\u00a0<\/em>partitioning makes an even distribution of data among predetermined number of partitions, In\u00a0RANGE\u00a0<\/em>and\u00a0LIST\u00a0<\/em>partitioning you must explicitly define the partitioning logic and which partition given column value or set of column values are stored. In\u00a0HASH\u00a0<\/em>partitioning MySQL take care of this, The following example explains\u00a0HASH\u00a0<\/em>partitioning better:<\/p>\nCREATE TABLE store (\r\n store_id INT NOT NULL,\r\n store_name VARCHAR(30),\r\n store_location VARCHAR(30),\r\n store_started DATE NOT NULL DEFAULT '1997-01-01',\r\n store_code INT\r\n)\r\nPARTITION BY HASH(store_id)\r\nPARTITIONS 4;<\/pre>\n
\n<\/strong>The\u00a0LINEAR HASH\u00a0<\/em>partitioning utilizes a linear powers-of-two algorithm, Where\u00a0HASH<\/em> partitioning\u00a0employs the modulus of the hashing function’s value. Please find below\u00a0LINEAR HASH<\/em> partitioning example:<\/p>\nCREATE TABLE store (\r\n store_id INT NOT NULL,\r\n store_name VARCHAR(30),\r\n store_location VARCHAR(30),\r\n store_started DATE NOT NULL DEFAULT '1997-01-01',\r\n store_code INT\r\n)\r\nPARTITION BY LINEAR HASH( YEAR(store_started) )\r\nPARTITIONS 4;<\/pre>\n
\n<\/strong>KEY <\/i>partitioning is very much similar to HASH<\/i>, the only difference is, the hashing function for the KEY <\/i>partitioning is supplied by MySQL, In case of MySQL NDB Cluster, MD5() is used, For tables using other storage engines, the MySQL server uses the storage engine specific hashing function which is based on the same algorithm as PASSWORD().<\/p>\nCREATE TABLE contact(\r\n id INT NOT NULL,\r\n name VARCHAR(20),\r\n contact_number INT,\r\n email VARCHAR(50),\r\n UNIQUE KEY (id)\r\n)\r\nPARTITION BY KEY()\r\nPARTITIONS 5;<\/pre>\n
\n<\/strong>SUBPARTITIONING \u00a0<\/i>is also known as composite partitioning, You can partition table combining RANGE <\/i>and HASH <\/i>for better results, The example below explains\u00a0SUBPARTITIONING <\/i>better:<\/p>\nCREATE TABLE purchase (id INT, item VARCHAR(30), purchase_date DATE)\r\n PARTITION BY RANGE( YEAR(purchase_date) )\r\n SUBPARTITION BY HASH( TO_DAYS(purchase_date) )\r\n SUBPARTITIONS 2 (\r\n PARTITION p0 VALUES LESS THAN (2000),\r\n PARTITION p1 VALUES LESS THAN (2010),\r\n PARTITION p2 VALUES LESS THAN MAXVALUE\r\n );<\/pre>\n
CREATE TABLE purchase (id INT, item VARCHAR(30), purchase_date DATE)\r\n PARTITION BY RANGE( YEAR(purchase_date) )\r\n SUBPARTITION BY HASH( TO_DAYS(purchase_date) ) (\r\n PARTITION p0 VALUES LESS THAN (2000) (\r\n SUBPARTITION s0,\r\n SUBPARTITION s1\r\n ),\r\n PARTITION p1 VALUES LESS THAN (2010) (\r\n SUBPARTITION s2,\r\n SUBPARTITION s3\r\n ),\r\n PARTITION p2 VALUES LESS THAN MAXVALUE (\r\n SUBPARTITION s4,\r\n SUBPARTITION s5\r\n )\r\n );\r\n<\/pre>\n
\n
CREATE TABLE purchase (id INT, item VARCHAR(30), purchase_date DATE)\r\n PARTITION BY RANGE( YEAR(purchase_date) )\r\n SUBPARTITION BY HASH( TO_DAYS(purchase_date) ) (\r\n PARTITION p0 VALUES LESS THAN (1990) (\r\n SUBPARTITION s0,\r\n SUBPARTITION s1\r\n ),\r\n PARTITION p1 VALUES LESS THAN (2000) (\r\n SUBPARTITION s2,\r\n SUBPARTITION s3\r\n ),\r\n PARTITION p2 VALUES LESS THAN MAXVALUE (\r\n SUBPARTITION s4,\r\n SUBPARTITION s5\r\n )\r\n );<\/pre>\n
\n<\/strong>MySQL partitioning also has limitations, We are listing down below the limitations of MySQL partitioning:<\/p>\nCREATE TABLE tab3 (\r\ncolumn1 INT NOT NULL,\r\ncolumn2 DATE NOT NULL,\r\ncolumn3 INT NOT NULL,\r\ncolumn4 INT NOT NULL,\r\nUNIQUE KEY (column1, column2),\r\nUNIQUE KEY (column3)\r\n)\r\nPARTITION BY HASH(column1 + column3)\r\nPARTITIONS 4;<\/pre>\n
CREATE TABLE table12 (\r\n column1 INT NOT NULL,\r\n column2 DATE NOT NULL,\r\n column3 INT NOT NULL,\r\n column4 INT NOT NULL,\r\n UNIQUE KEY (column1, column2, column3)\r\n)\r\nPARTITION BY HASH(column3)\r\nPARTITIONS 5;<\/pre>\n
CREATE TABLE table25 (\r\n column11 INT NOT NULL,\r\n column12 DATE NOT NULL,\r\n column13 INT NOT NULL,\r\n column14 INT NOT NULL,\r\n UNIQUE KEY (column11, column13)\r\n)\r\nPARTITION BY HASH(column11 + column13)\r\nPARTITIONS 5;<\/pre>\n
CREATE TABLE table55 (\r\n column11 INT NOT NULL,\r\n column12 DATE NOT NULL,\r\n column13 INT NOT NULL,\r\n column14 INT NOT NULL,\r\n PRIMARY KEY(column11, column12)\r\n)\r\nPARTITION BY HASH(column13)\r\nPARTITIONS 4;\r\n<\/pre>\n
CREATE TABLE table65 (\r\n column20 INT NOT NULL,\r\n column25 DATE NOT NULL,\r\n column30 INT NOT NULL,\r\n column35 INT NOT NULL,\r\n PRIMARY KEY(column20, column30),\r\n UNIQUE KEY(column25)\r\n)\r\nPARTITION BY HASH( YEAR(column25) )\r\nPARTITIONS 5;<\/pre>\n
CREATE TABLE t45 (\r\n column50 INT NOT NULL,\r\n column55 DATE NOT NULL,\r\n column60 INT NOT NULL,\r\n column65 INT NOT NULL,\r\n PRIMARY KEY(column50, column55)\r\n)\r\nPARTITION BY HASH(column50 + YEAR(column55))\r\nPARTITIONS 5;<\/pre>\n
CREATE TABLE table88 (\r\n column80 INT NOT NULL,\r\n column81 DATE NOT NULL,\r\n column82 INT NOT NULL,\r\n column83 INT NOT NULL,\r\n PRIMARY KEY(column80, column81, column82),\r\n UNIQUE KEY(column81, column82)\r\n);<\/pre>\n
CREATE TABLE table_has_no_pk (column10 INT, column11 INT, column12 varchar(20))\r\nPARTITION BY RANGE(column10) (\r\nPARTITION p0 VALUES LESS THAN (500),\r\nPARTITION p1 VALUES LESS THAN (600),\r\nPARTITION p2 VALUES LESS THAN (700),\r\nPARTITION p3 VALUES LESS THAN (800)\r\n);<\/pre>\n
ALTER TABLE table_has_no_pk ADD PRIMARY KEY(column10);<\/pre>\n
ALTER TABLE table_has_no_pk drop primary key;<\/pre>\n
ALTER TABLE table_has_no_pk ADD PRIMARY KEY(column10,column11);<\/pre>\n
ALTER TABLE table_has_no_pk drop primary key;<\/pre>\n
mysql> ALTER TABLE table_has_no_pk ADD PRIMARY KEY(column11);\r\nERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function\r\nmysql> \r\n<\/pre>\n
\n
\n
\n
\n
\n
\n
\n
\n
\n