CODEWITHSUNDEEP
mysqlrdbmspartitioning
Khushal
Khushal

Reputation: 176

How to DROP Sub Partition in specific Partition in Mysql?

I have created partitions and sub-partitions in my table. My Partitions are like below : 

PARTITION BY RANGE ( MONTH(record_date))
SUBPARTITION BY HASH (DAY(record_date))
(
PARTITION p2012 VALUES LESS THAN (5)
(
    SUBPARTITION 01_04 ENGINE = InnoDB,
    SUBPARTITION 02_04 ENGINE = InnoDB,
    SUBPARTITION 03_04 ENGINE = InnoDB,
    SUBPARTITION 04_04 ENGINE = InnoDB,
    SUBPARTITION 05_04 ENGINE = InnoDB,
    SUBPARTITION 06_04 ENGINE = InnoDB,
    SUBPARTITION 07_04 ENGINE = InnoDB,
    SUBPARTITION 08_04 ENGINE = InnoDB,
    SUBPARTITION 09_04 ENGINE = InnoDB,
    SUBPARTITION 10_04 ENGINE = InnoDB,
    SUBPARTITION 11_04 ENGINE = InnoDB,
    SUBPARTITION 12_04 ENGINE = InnoDB,
    SUBPARTITION 13_04 ENGINE = InnoDB,
    SUBPARTITION 14_04 ENGINE = InnoDB,
    SUBPARTITION 15_04 ENGINE = InnoDB,
    SUBPARTITION 16_04 ENGINE = InnoDB,
    SUBPARTITION 17_04 ENGINE = InnoDB,
    SUBPARTITION 18_04 ENGINE = InnoDB,
    SUBPARTITION 19_04 ENGINE = InnoDB,
    SUBPARTITION 20_04 ENGINE = InnoDB,
    SUBPARTITION 21_04 ENGINE = InnoDB,
    SUBPARTITION 22_04 ENGINE = InnoDB,
    SUBPARTITION 23_04 ENGINE = InnoDB,
    SUBPARTITION 24_04 ENGINE = InnoDB,
    SUBPARTITION 25_04 ENGINE = InnoDB,
    SUBPARTITION 26_04 ENGINE = InnoDB,
    SUBPARTITION 27_04 ENGINE = InnoDB,
    SUBPARTITION 28_04 ENGINE = InnoDB,
    SUBPARTITION 29_04 ENGINE = InnoDB,
    SUBPARTITION 30_04 ENGINE = InnoDB,
    SUBPARTITION 31_04 ENGINE = InnoDB
  ),
  PARTITION p2013 VALUES LESS THAN (6)
  (
  SUBPARTITION 01_05 ENGINE = InnoDB,
  SUBPARTITION 02_05 ENGINE = InnoDB,
  SUBPARTITION 03_05 ENGINE = InnoDB,
  SUBPARTITION 04_05 ENGINE = InnoDB,
  SUBPARTITION 05_05 ENGINE = InnoDB,
  SUBPARTITION 06_05 ENGINE = InnoDB,
  SUBPARTITION 07_05 ENGINE = InnoDB,
  SUBPARTITION 08_05 ENGINE = InnoDB,
  SUBPARTITION 09_05 ENGINE = InnoDB,
  SUBPARTITION 10_05 ENGINE = InnoDB,
  SUBPARTITION 11_05 ENGINE = InnoDB,
  SUBPARTITION 12_05 ENGINE = InnoDB,
  SUBPARTITION 13_05 ENGINE = InnoDB,
  SUBPARTITION 14_05 ENGINE = InnoDB,
  SUBPARTITION 15_05 ENGINE = InnoDB,
  SUBPARTITION 16_05 ENGINE = InnoDB,
  SUBPARTITION 17_05 ENGINE = InnoDB,
  SUBPARTITION 18_05 ENGINE = InnoDB,
  SUBPARTITION 19_05 ENGINE = InnoDB,
  SUBPARTITION 20_05 ENGINE = InnoDB,
  SUBPARTITION 21_05 ENGINE = InnoDB,
  SUBPARTITION 22_05 ENGINE = InnoDB,
  SUBPARTITION 23_05 ENGINE = InnoDB,
  SUBPARTITION 24_05 ENGINE = InnoDB,
  SUBPARTITION 25_05 ENGINE = InnoDB,
  SUBPARTITION 26_05 ENGINE = InnoDB,
  SUBPARTITION 27_05 ENGINE = InnoDB,
  SUBPARTITION 28_05 ENGINE = InnoDB,
  SUBPARTITION 29_05 ENGINE = InnoDB,
  SUBPARTITION 30_05 ENGINE = InnoDB,
  SUBPARTITION 31_05 ENGINE = InnoDB
  )

)

Now, I want to delete some partition using below Alter Query : 

alter table my_records drop partition 30_04;

It gives me following error :

ERROR 1507 (HY000): Error in list of partitions to DROP

What is the syntax to drop sub-partition. I don't want to use Reorganize Syntax.

Upvotes: 0

Views: 739

Answers (1)

Rick James
Rick James

Reputation: 142518

Rather than tackle your specific problem, let me point out that your partition+subpartition design is probably useless.

  • BY RANGE ( MONTH(...) ) does not provide any performance. Furthermore, if you have a date range, it will probably pick all partitions.
  • BY HASH (...) does not provide any performance. And, again, a range would pick all subpartitions.

Please explain what you are trying to achieve. It may be possible that you have some novel benefit of partitioning that I have not yet discovered.

Consider a simple PARTITION BY RANGE (TO_DAYS(...)) with no SUBPARTITIONs. It's main advantage is in DROPping the oldest partition (as opposed to a slow DELETE). It does not provide a speedup for normal SELECTs.

More discussion.

Upvotes: 1

Related Questions