1. General Syntax for Partitioning
CREATE TABLE table_name (
column_definitions
)
PARTITION BY partition_type(partition_column) (
PARTITION partition_name1 VALUES partition_definition1,
PARTITION partition_name2 VALUES partition_definition2,
…
);
2. RANGE Partitioning:- Divide sales records by year.
Example:-
CREATE TABLE sales (
id INT,
sale_year INT,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (sale_year) (
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
To Insert :
INSERT INTO sales VALUES (1, 2018, 1200.00); — Goes to p2019
INSERT INTO sales VALUES (2, 2020, 1500.00); — Goes to p2020
INSERT INTO sales VALUES (3, 2022, 2000.00); — Goes to pmax
Explanation:
- Record 1 goes to p2019 (2018 < 2020)
- Record 2 goes to p2020 (2020 < 2021)
- Record 3 goes to pmax (2022 >= 2022)
3. LIST Partitioning
Store data based on region.
Example:-
CREATE TABLE employees (
id INT,
name VARCHAR(100),
region VARCHAR(20)
)
PARTITION BY LIST COLUMNS(region) (
PARTITION east VALUES IN (‘East’, ‘Northeast’),
PARTITION west VALUES IN (‘West’, ‘Southwest’),
PARTITION central VALUES IN (‘Central’)
);
To Insert:
INSERT INTO employees VALUES (1, ‘Arun’, ‘East’); — goes to east
INSERT INTO employees VALUES (2, ‘Mira’, ‘West’); — goes to west
INSERT INTO employees VALUES (3, ‘Ravi’, ‘Central’); — goes to central
INSERT INTO employees VALUES (4, ‘Lina’, ‘North’); — ❌ Error (no matching partition)
Explanation:
- Arun → east, Mira → west, Ravi → central
- Lina fails because ‘North’ is not listed in any partition.
4. HASH Partitioning
Evenly distribute rows across partitions for better load balancing.
Example:-
CREATE TABLE logs (
id INT,
log_message TEXT
)
PARTITION BY HASH(id)
PARTITIONS 4;
To Insert:
INSERT INTO logs VALUES (1, ‘User login’);
INSERT INTO logs VALUES (2, ‘User logout’);
INSERT INTO logs VALUES (3, ‘Error message’);
INSERT INTO logs VALUES (4, ‘New signup’);
Explanation:
Each row is placed in one of the 4 partitions based on the hash of id.
MySQL decides internally: id % 4, for example:
id 1 → partition 1
id 2 → partition 2
id 3 → partition 3
id 4 → partition 0
5. KEY Partitioning
Similar to HASH, but MySQL chooses the hash function internally.
Example:-
CREATE TABLE customers (
id INT NOT NULL,
name VARCHAR(100)
)
PARTITION BY KEY(id)
PARTITIONS 3;
To Insert:
INSERT INTO customers VALUES (101, ‘Karan’);
INSERT INTO customers VALUES (102, ‘Meena’);
INSERT INTO customers VALUES (103, ‘Ramesh’);
Explanation:
MySQL uses an internal hash of the id to decide the partition.
You don’t need to define the hash function — it’s automatically handled.
6. Viewing Partitions
To check which partitions exist in a table:
Example:-
SELECT table_name, partition_name, subpartition_name, table_rows
FROM information_schema.partitions
WHERE table_schema = ‘your_database’ AND table_name = ‘sales’;
Explanation:
Lists partition details: how many rows each partition holds, names, etc.
7. Dropping a Partition
You can drop data in a specific partition
Syntax:-
ALTER TABLE table_name DROP PARTITION partition_name;
Example:-
ALTER TABLE sales DROP PARTITION p2020;
Explanation:
- Removes all records stored in partition p2020.
- The structure remains intact — only that partition’s data is erased.