{"id":1037,"date":"2017-08-14T16:22:55","date_gmt":"2017-08-14T21:22:55","guid":{"rendered":"http:\/\/xfloyd.net\/blog\/?p=1037"},"modified":"2017-08-14T16:22:55","modified_gmt":"2017-08-14T21:22:55","slug":"partitioning-on-mysql","status":"publish","type":"post","link":"http:\/\/xfloyd.net\/blog\/?p=1037","title":{"rendered":"Partitioning on MySql"},"content":{"rendered":"
\n

Imagine you need to execute one ore more queries on a big size table, containing the history of the sales of an e-commerce point.<\/p>\n

If your hardware resources are limited, scanning the whole table could require several minutes, and this in turn could imply long table locks and waste of time to the users.<\/p>\n

Since version 5.1, MySql supports partitioning, a mechanism allowing data to be divided according to access needs.<\/p>\n

There are several partitioning types. The most popular mechanisms are:<\/p>\n

– RANGE: <\/span>rows are divided based upon a specified column range<\/p>\n

– HASH: <\/span>the column hash is calculated based upon the record position in the <\/span>partition resulting from the operation <\/span><\/p>\n

– LIST: <\/span>works like RANGE but the values are not surely adjacent<\/p>\n

The partitioning of a table works in two different manners:<\/p>\n

– during <\/span>the <\/span>table creation<\/p>\n

CREATE TABLE sales (\r\n    id int NOT NULL,\r\n    order_date DATETIME NOT NULL,\r\n    user bigint NOT NULL,\r\n    total float NOT NULL DEFAULT '0',\r\n    receipt_id bigint NULL\r\n) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) ( \r\n  PARTITION p_history VALUES LESS THAN (2014), \r\n  PARTITION p_data VALUES LESS THAN MAXVALUE \r\n);<\/code><\/pre>\n
during  <\/span>the  <\/span>modification of the table structure<\/p>\n
ALTER TABLE sales PARTITION BY RANGE(YEAR(order_date)) (\r\n\tPARTITION p_history VALUES LESS THAN (2014),\r\n\tPARTITION p_data VALUES LESS THAN MAXVALUE\r\n);<\/code><\/pre>\n
An important tool for debugging the extraction operations is the following command<\/p>\n
EXPLAIN PARTITIONS;<\/code><\/pre>\n
\"\"<\/p>\n
The command output shows the partition used by MySql for execution of the specified query.<\/p>\n
The partitioning is a very effective tool, but it may give no results, if you don’t choose the proper partitioning strategy: partitioning a sales table according to the creation date and then filtering data based on users, might be useless, independently on the partition criteria, or could even worst the situation.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
Imagine you need to execute one ore more queries on a big size table, containing the history of the sales of an e-commerce point. If your hardware resources are limited, scanning the whole table could require several minutes, and this in turn could imply long table locks and waste of time to the users. Since […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"_links":{"self":[{"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1037"}],"collection":[{"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1037"}],"version-history":[{"count":1,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1037\/revisions"}],"predecessor-version":[{"id":1038,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1037\/revisions\/1038"}],"wp:attachment":[{"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1037"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1037"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1037"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}