Table partitions in cloud-sql ?

[Venkat Vankayala]

Hello ,

Can we create Table partitions in cloud-sql ? Any document on this would be a great help. 

Thanks 

[Georgi Sotirov]

Hello,

At least I do not see any restriction on table partitioning mentioned in Unsupported MySQL features. The output from SHOW PLUGINS or INFORMATION_SCHEMA.PLUGINS shows support for InnoDB (i.e. "the only supported storage engine" and which supports table partitioning) as expected and partition (later is only for 5.7 as in 8.0 table partitioning is handled by the storage engine). I tried the following SQL on a Cloud SQL MySQL 5.7 instance:

CREATE TABLE part_test (

id         INT NOT NULL AUTO_INCREMENT,

`name`     VARCHAR(32) NOT NULL,

birth_date DATE NOT NULL,

PRIMARY KEY (id, birth_date)

)

ENGINE = InnoDB

PARTITION BY RANGE( YEAR(birth_date) )

(

PARTITION part0 VALUES LESS THAN (1960),

PARTITION part1 VALUES LESS THAN (1970),

PARTITION part2 VALUES LESS THAN (1980),

PARTITION part3 VALUES LESS THAN (1990),

PARTITION part4 VALUES LESS THAN (2000),

PARTITION part5 VALUES LESS THAN (2010),

PARTITION part6 VALUES LESS THAN (MAXVALUE)

);

And it was executed successfully. I then tested with:

INSERT INTO part_test (`name`, birth_date)
VALUES ('Person 1', '1979-09-20'),
       ('Person 2', '2005-12-12');

And finally checked that partitions are properly populated:

SELECT partition_name, partition_method, part_description, table_rows
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'part_test';

+----------------+------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+-----------------------+------------+
| part0          | RANGE            | 1960                  |          0 |
| part1          | RANGE            | 1970                  |          0 |
| part2          | RANGE            | 1980                  |          1 |
| part3          | RANGE            | 1990                  |          0 |
| part4          | RANGE            | 2000                  |          0 |
| part5          | RANGE            | 2010                  |          1 |
| part6          | RANGE            | MAXVALUE              |          0 |
+----------------+------------------+-----------------------+------------+
7 rows in set (0.05 sec)

You could find information about table partitioning in MySQL in the reference manual for 5.7 and 8.0 respectively.

I hope this helps.

Regards,

--

Georgi D. Sotirov