Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Optimize large tables in MariaDB Server with partitioning. Learn how to divide tables into smaller, manageable parts for improved performance, easier maintenance, and scalability.
Understand how the optimizer automatically prunes irrelevant partitions and how to explicitly select partitions in your queries for efficiency.
EXPLAIN PARTITIONS SELECT * FROM orders WHERE id < 15000000;
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | orders | p0,p1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+SELECT * FROM orders PARTITION (p3) WHERE user_id = 50;
SELECT * FROM orders PARTITION (p2,p3) WHERE user_id >= 40;Learn how MariaDB stores partitioned tables on the filesystem, typically creating separate .ibd files for each partition when using InnoDB.
Understand how to retrieve metadata about partitions using the INFORMATION_SCHEMA.PARTITIONS table to monitor row counts and storage usage.
Discover these variants that allow partitioning based on multiple columns and non-integer types, offering greater flexibility than standard RANGE/LIST.
CREATE OR REPLACE TABLE t1 (c1 INT, c2 DATETIME)
PARTITION BY LINEAR HASH(TO_DAYS(c2))
PARTITIONS 5;orders.frm
orders.par
orders#P#p0.ibd
orders#P#p1.ibd
orders#P#p2.ibd
orders#P#p3.ibdPARTITION BY KEY ([column_names])
[PARTITIONS (number_of_partitions)]PARTITION BY KEY
[ALGORITHM={MYSQL51|MYSQL55|BASE31|CRC32C|XXH32|XXH3}]
([column_names])
[PARTITIONS (number_of_partitions)]orders.frm
orders.par
orders#P#p0.MYD
orders#P#p0.MYI
orders#P#p1.MYD
orders#P#p1.MYI
orders#P#p2.MYD
orders#P#p2.MYI
orders#P#p3.MYD
orders#P#p3.MYI ERROR 1488 (HY000): Field in list of fields for partition function not found in tableCREATE OR REPLACE TABLE t1 (v1 INT)
PARTITION BY KEY (v1)
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (v1 INT, v2 INT)
PARTITION BY KEY (v1,v2)
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
id INT NOT NULL UNIQUE KEY,
name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
id INT NULL UNIQUE KEY,
name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;
ERROR 1488 (HY000): Field in list of fields for partition function not found in tableCREATE OR REPLACE TABLE t1 (
id INT NULL UNIQUE KEY,
name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;
ERROR 1488 (HY000): Field in list of fields for partition function not found in tableCREATE OR REPLACE TABLE t1 (
id INT NULL UNIQUE KEY,
name VARCHAR(5)
)
PARTITION BY KEY(name)
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
a VARCHAR(10),
b VARCHAR(10),
c VARCHAR(10),
PRIMARY KEY (a(5), b, c(5))
) PARTITION BY KEY() PARTITIONS 2;
CREATE OR REPLACE TABLE t1 (
a VARCHAR(10),
b VARCHAR(10),
c VARCHAR(10),
PRIMARY KEY (b)
) PARTITION BY KEY() PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
a VARCHAR(10),
b VARCHAR(10),
c VARCHAR(10),
PRIMARY KEY (a(5), b(5), c(5))
) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning functionPARTITION BY LIST (partitioning_expression)
(
PARTITION partition_name VALUES IN (value_list),
[ PARTITION partition_name VALUES IN (value_list), ... ]
[ PARTITION partition_name DEFAULT ]
)CREATE OR REPLACE TABLE t1 (
num TINYINT(1) NOT NULL
)
ENGINE = InnoDB
PARTITION BY LIST (num) (
PARTITION p0 VALUES IN (0,1),
PARTITION p1 VALUES IN (2,3),
PARTITION p2 DEFAULT
);PARTITION BY RANGE COLUMNS (col1, col2, ...)
(
PARTITION partition_name VALUES LESS THAN (value1, value2, ...),
[ PARTITION partition_name VALUES LESS THAN (value1, value2, ...), ... ]
)PARTITION BY LIST COLUMNS (partitioning_expression)
(
PARTITION partition_name VALUES IN (value1, value2, ...),
[ PARTITION partition_name VALUES IN (value1, value2, ...), ... ]
[ PARTITION partititon_name DEFAULT ]
)CREATE OR REPLACE TABLE t1 (
date1 DATE NOT NULL,
date2 DATE NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (date1,date2) (
PARTITION p0 VALUES LESS THAN ('2013-01-01', '1994-12-01'),
PARTITION p1 VALUES LESS THAN ('2014-01-01', '1995-12-01'),
PARTITION p2 VALUES LESS THAN ('2015-01-01', '1996-12-01')
);CREATE OR REPLACE TABLE t1 (
num TINYINT(1) NOT NULL
)
ENGINE = InnoDB
PARTITION BY LIST COLUMNS (num) (
PARTITION p0 VALUES IN (0,1),
PARTITION p1 VALUES IN (2,3),
PARTITION p2 DEFAULT
);ALTER TABLE table_name REBUILD PARTITION partition_namesALTER TABLE table_name OPTIMIZE PARTITION partition_namesALTER TABLE table_name ANALYZE PARTITION partition_namesALTER TABLE table_name REPAIR PARTITION partition_namesALTER TABLE table_name CHECK PARTITION partition_namesALTER TABLE table_name TRUNCATE PARTITION partition_namesALTER TABLE table_name REORGANIZE PARTITION partition_names INTO (PARTITION partition_definition, ...)ALTER TABLE tbl REORGANIZE PARTITION p_future INTO (PARTITION p_2026 VALUES LESS THAN (2027), PARTITION p_future VALUES LESS THAN MAXVALUE)RANGE partitioning, the syntax is the following:PARTITION BY RANGE (partitioning_expression)
(
PARTITION partition_name VALUES LESS THAN (value),
[ PARTITION partition_name VALUES LESS THAN (value), ... ]
[ PARTITION partition_name VALUES LESS THAN MAXVALUE ]
)CREATE TABLE log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
dt DATETIME NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, dt)
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);CREATE TABLE log2
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ts TIMESTAMP NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, ts)
)
ENGINE = InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(ts))
(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01 00:00:00'))
);ALTER TABLE log DROP PARTITION p0;INSERT INTO log(id,dt) VALUES
(1, '2016-01-01 01:01:01'),
(2, '2015-01-01 01:01:01');
ERROR 1526 (HY000): Table has no partition for value 2016INSERT IGNORE INTO log(id,dt) VALUES
(1, '2016-01-01 01:01:01'),
(2, '2015-01-01 01:01:01');
SELECT * FROM log;
+----+---------------------+------+
| id | timestamp | user |
+----+---------------------+------+
| 2 | 2015-01-01 01:01:01 | NULL |
+----+---------------------+------+CREATE TABLE log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
dt DATETIME NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, dt)
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016),
PARTITION p4 VALUES LESS THAN MAXVALUE
);Complete Partitioning Overview guide for MariaDB. Complete reference documentation for implementation, configuration, and usage for production use.
WITHOUT VALIDATION option is specified.VALUES I.SHOW PLUGINS;
...
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+| partition | DISABLED | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+--skip-partition
--disable-partition
--partition=OFFADD PARTITION [IF NOT EXISTS] (partition_definition)CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 ADD PARTITION (
PARTITION p4 VALUES LESS THAN (2017),
PARTITION p5 VALUES LESS THAN (2018)
);ALTER TABLE t1 ADD PARTITION (
PARTITION p0a VALUES LESS THAN (2012)
);
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partitionCOALESCE PARTITION numberCREATE OR REPLACE TABLE t1 (v1 INT)
PARTITION BY KEY (v1)
PARTITIONS 5;ALTER TABLE t1 COALESCE PARTITION 2;CONVERT PARTITION partition_name TO TABLE tbl_name
CONVERT TABLE normal_table TO partition_definitionCREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
INSERT INTO t1 VALUES ('2013-11-11'),('2014-11-11'),('2015-11-11');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
| 2015-11-11 00:00:00 |
+---------------------+
ALTER TABLE t1 CONVERT PARTITION p3 TO TABLE t2;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
+---------------------+
SELECT * FROM t2;
+--------------+
| dt |
+--------------+
| 2015-11-11 00:00:00 |
+---------------------+
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (year(`dt`))
(PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB)
SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
TABLE: t2
CREATE TABLE: CREATE TABLE `t2` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ciALTER TABLE t1 CONVERT TABLE t2 TO PARTITION p3 VALUES LESS THAN (2016);
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
| 2015-11-11 00:00:00 |
+---------------------+
3 rows in set (0.001 sec)
SELECT * FROM t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (year(`dt`))
(PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN (2016) ENGINE = InnoDB)CONVERT TABLE normal_table TO partition_definition [{WITH | WITHOUT} VALIDATION]CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
INSERT INTO t1 VALUES ('2013-11-11'),('2014-11-11'),('2015-11-11');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
| 2015-11-11 00:00:00 |
+---------------------+
CREATE OR REPLACE TABLE t2 LIKE t1;
ALTER TABLE t2 REMOVE PARTITIONING;
ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE t2;
ALTER TABLE t1 DROP PARTITION p3;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
+---------------------+
SELECT * FROM t2;
+--------------+
| dt |
+--------------+
| 2015-11-11 00:00:00 |
+---------------------+
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (year(`dt`))
(PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB)
SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
TABLE: t2
CREATE TABLE: CREATE TABLE `t2` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ciALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2016));
ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE t2;
DROP TABLE t2;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
| 2015-11-11 00:00:00 |
+---------------------+
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (year(`dt`))
(PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN (2016) ENGINE = InnoDB)DROP PARTITION [IF EXISTS] partition_namesCREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
INSERT INTO t1 VALUES ('2012-11-15');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2012-11-15 00:00:00 |
+---------------------+
ALTER TABLE t1 DROP PARTITION p0;
SELECT * FROM t1;
Empty set (0.002 sec)EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]EXCHANGE PARTITION partition_name WITH TABLE tbl_nameCREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014)
);
CREATE OR REPLACE TABLE t2 (
dt DATETIME NOT NULL
) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('2012-01-01'),('2013-01-01');
INSERT INTO t2 VALUES ('2013-02-02');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2012-01-01 00:00:00 |
| 2013-01-01 00:00:00 |
+---------------------+
SELECT * FROM t2;
+--------------+
| dt |
+--------------+
| 2013-02-02 00:00:00 |
+---------------------+
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2012-01-01 00:00:00 |
| 2013-02-02 00:00:00 |
+---------------------+
SELECT * FROM t2;
+--------------+
| dt |
+--------------+
| 2013-01-01 00:00:00 |
+---------------------+CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014)
);
CREATE OR REPLACE TABLE t2 (
dt DATETIME NOT NULL
) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('2012-02-02'),('2013-03-03');
INSERT INTO t2 VALUES ('2015-05-05');
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;ERROR 1526 (HY000): Table has no partition for value 0ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2 WITHOUT VALIDATION;Query OK, 0 rows affected (0.048 sec)REMOVE PARTITIONINGALTER TABLE t1 REMOVE PARTITIONING;REORGANIZE PARTITION [partition_names INTO (partition_definitions)]CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
PARTITION p0a VALUES LESS THAN (2012),
PARTITION p0b VALUES LESS THAN (2013)
);CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
ALTER TABLE t1 REORGANIZE PARTITION p4 INTO (
PARTITION p4 VALUES LESS THAN (2017),
PARTITION p5 VALUES LESS THAN MAXVALUE
);CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 REORGANIZE PARTITION p2,p3 INTO (
PARTITION p2 VALUES LESS THAN (2016)
);CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (
PARTITION p3 VALUES LESS THAN (2017)
);CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (
PARTITION p3_new VALUES LESS THAN (2016)
);TRUNCATE PARTITION partition_namesCREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
INSERT INTO t1 VALUES ('2012-11-01'),('2013-11-02'),('2014-11-03'),('2015-11-04');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2012-11-01 00:00:00 |
| 2013-11-02 00:00:00 |
| 2014-11-03 00:00:00 |
| 2015-11-04 00:00:00 |
+---------------------+
ALTER TABLE t1 TRUNCATE PARTITION p0,p2;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-02 00:00:00 |
| 2015-11-04 00:00:00 |
+---------------------+ALTER TABLE t1 ANALYZE PARTITION p0,p1,p3;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+CHECK PARTITION {ALL | PARTITION [,partition2 ...]}ALTER TABLE t1 CHECK PARTITION p1,p3;
+---------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status | OK |
+---------+-------+----------+----------+REPAIR PARTITION {ALL | partition [,partition2 ...]} [QUICK] [EXTENDED]ALTER TABLE t1 REPAIR PARTITION p0,p3;
+---------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+----------+
| test.t1 | repair | status | OK |
+---------+--------+----------+----------+OPTIMIZE PARTITION {ALL | PARTITION [,partition2 ...]}ALTER TABLE t1 OPTIMIZE PARTITION p0,p3;
+---------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t1 | optimize | status | OK |
+---------+----------+----------+----------+Explore different partitioning types for MariaDB Server tables. Understand range, list, hash, and key partitioning to optimize data management and improve query performance.
MOD(partitioning_expression, number_of_partitions)CREATE OR REPLACE TABLE t1 (c1 INT, c2 DATETIME)
PARTITION BY HASH(TO_DAYS(c2))
PARTITIONS 5;INSERT INTO t1 VALUES (1,'2023-11-15');
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 0 |
| p4 | 1 |
+----------------+------------+Learn about LINEAR KEY partitioning, which combines the internal key hashing with a linear algorithm for efficient partition handling.
CREATE OR REPLACE TABLE t1 (v1 INT)
PARTITION BY LINEAR KEY (v1)
PARTITIONS 2;