Partitioning tables in MySQL is splitting a large table into parts according to the specified criteria, which will speed up reading and writing data to it if this table contains a very large amount of data.
I will give a simple example of creating a new table with partitioning:
CREATE DATABASE ixnfo;
use ixnfo;
CREATE TABLE ixnfo (
id INT,
name VARCHAR(30),
date DATE) ENGINE = INNODB
PARTITION BY RANGE( YEAR(date) ) (
PARTITION p_old VALUES LESS THAN(2019),
PARTITION p_2019 VALUES LESS THAN(2020),
PARTITION p_2020 VALUES LESS THAN(MAXVALUE)
);
Let’s check:
SHOW CREATE TABLE ixnfo\G
A simple example of partitioning an already created table:
CREATE TABLE ixnfo2 (
id INT,
name VARCHAR(30),
date DATE) ENGINE = INNODB;
SHOW CREATE TABLE ixnfo2\G
ALTER TABLE ixnfo2 PARTITION BY RANGE( YEAR(date) ) (
PARTITION p_old VALUES LESS THAN(2019),
PARTITION p_2019 VALUES LESS THAN(2020),
PARTITION p_2020 VALUES LESS THAN(MAXVALUE)
);
SHOW CREATE TABLE ixnfo2\G
It should be noted that disk space is required for the copy of the table, since a copy of the table with partitioning will be created, and then replaced with the current one.
The name of the partitioned table remains the same, that is, for example, SELECT queries can be executed as before, for example:
INSERT INTO ixnfo VALUES ('1','ixnfo.com','2019-05-05');
INSERT INTO ixnfo VALUES ('2','ixnfo.com','2020-05-05');
INSERT INTO ixnfo VALUES
('1','ixnfo.com','2018-05-05'),
('2','ixnfo.com','2020-05-05'),
('3','ixnfo.com','2021-05-05');
SELECT * FROM ixnfo;
SELECT * FROM ixnfo WHERE date LIKE "%2019%";
SELECT * FROM ixnfo WHERE date LIKE "%2020%";
SELECT * FROM ixnfo WHERE date="2020-05-05";
SELECT * FROM ixnfo WHERE date BETWEEN '2019-01-01' AND '2020-01-01';
See my other articles about MySQL