JWan Wiki

Partitioning help partitioned the table into smaller tables. It is best use if the table is expected to be huge.

Best to create the partition during creation of the table, however, for existing table, create a new table using partition, copy the data, then just rename the table.

Sample to create partition:

CREATE TABLE serial (
	SerialID INT(11) NOT NULL AUTO_INCREMENT,
	ItemSerial VARCHAR(30) NULL DEFAULT '' COLLATE 'latin1_swedish_ci',
	PRIMARY KEY (`SerialID`) USING BTREE,
	INDEX itemserial (ItemSerial) USING BTREE;
)
COLLATE='utf8mb4_general_ci'
AUTO_INCREMENT=1
PARTITION BY RANGE (SerialID)
(PARTITION `p005m` VALUES LESS THAN (500000) ENGINE = MyISAM,
 PARTITION `p010m` VALUES LESS THAN (1000000) ENGINE = MyISAM,
 PARTITION `p015m` VALUES LESS THAN (1500000) ENGINE = MyISAM,
 PARTITION `p020m` VALUES LESS THAN (2000000) ENGINE = MyISAM,
 PARTITION `p100m` VALUES LESS THAN MAXVALUE ENGINE = MyISAM);

Can also be good if partitioned by date/time:

    PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp)) (
    PARTITION p_historic VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01')),
    PARTITION p_2020 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01')),
    PARTITION p_2021 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01')),
    PARTITION p_2022 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01')),
    PARTITION p_2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE);

or

    PARTITION BY RANGE (YEAR(timestamp)) (
    PARTITION p_pre2020 VALUES LESS THAN (2020),
    PARTITION p_2020 VALUES LESS THAN (2021),
    PARTITION p_2021 VALUES LESS THAN (2022),
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE);

can also be partitioned by list:

    PARTITION BY LIST (customer_id) (
    PARTITION p_cust_1 VALUES IN (1, 2, 3),
    PARTITION p_cust_2 VALUES IN (4, 5, 6),
    PARTITION p_cust_3 VALUES IN (7, 8, 9),
    PARTITION p_others VALUES IN (DEFAULT));

or by name initial:

    PARTITION BY RANGE COLUMNS(customer_name) (
    PARTITION p_a_f VALUES LESS THAN ('G'),
    PARTITION p_g_m VALUES LESS THAN ('N'),
    PARTITION p_n_s VALUES LESS THAN ('T'),
    PARTITION p_t_z VALUES LESS THAN (MAXVALUE));

File sizes of individual partition are not updated until they are optimized:

OPTIMIZE TABLE newtable;