CODEWITHSUNDEEP
mysqlpartitioning
MySQL DBA
MySQL DBA

Reputation: 5822

Create subpartitioning in MySQL

I want to create a partitioning and subpartitioning on my MySQL table in order to optimize the performance of the table.

For Ex :- 

Create table mytest (id int not null, mydate date)
PARTITION BY LIST (id)
SUBPARTITION BY RANGE (TO_DAYS(mydate))
(
PARTITION P01 VALUES IN (1,2,5,6,8,10)
(
SUBPARTITION S01 VALUES LESS THAN ('2011-10-23'),
SUBPARTITION S02 VALUES LESS THAN ('2011-10-16'),
SUBPARTITION S03 VALUES LESS THAN ('2011-10-09')
));

like this i m trying to create the subpartitioning but getting an error which says incorrect syntax near RANGE.

Can anyone help me with the info whether PARTITION BY LIST AND SUBPARTITION BY RANGE is allowed.

Upvotes: 0

Views: 3215

Answers (1)

afuzzyllama
afuzzyllama

Reputation: 6548

You cannot subpartition by RANGE. You can only subpartition by HASH or KEY. It is stated in the documentation:

"In MySQL 5.1, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning."

Upvotes: 4

Related Questions