I have a MySQL InnoDB table PARTITIONed by HASH(DAY(dateTime)). I'd like to remove partitions holding data older than 10 days.

CREATE TABLE `partitioned` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dateTime` datetime NOT NULL,
  `sourceId` int(10) unsigned NOT NULL,
  `destinationId` int(10) unsigned NOT NULL,
  `times` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`,`dateTime`),
  UNIQUE KEY `key` (`dateTime`,`sourceId`,`destinationId`) USING BTREE,  

I've tried some queries:

EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE DAY(`dateTime`) IN (1,2,3,4);
EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE `dateTime` < '2015-01-01';

EXPLAIN for those two is showing that all partitions will be inspected. Not good!

EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE `dateTime` IN ('2015-01-01','2015-01-02','2015-01-03','2015-01-04');

EXPLAIN for this one is showing that only required partitions will be inspected. I suppose that DELETE will also touch only required partitions, right?

Questions are:

  • So, what is the fastest available way to DELETE from table partitioned by HASH?
  • Is it possible to get name of partition holding DAY? Which function is MYSQL using for partitioning using HASH(DAY())?

Or it is a bad idea and I should move to RANGE partitioning with DROPing and CREATing partitions on daily basis?

UPD: I see http://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations.html that for MySQL >= 5.5.0 it is possible to TRUNCATE PARTITION, but what if I have AUTO_INCREMENT field in partitioned table? Is TRUNCATE PARTITION reseting auto_increment field or what?

UPD2: I'll try to answer my question. Ok, we can get name of partition by EXPLAIN PARTITIONS SELECT * FROM partitioned WHERE dateTime IN ("2011-01-01"). As soon as we know name of partition, we can do ALTER TABLE partitioned TRUNCATE PARTITION {partitionName}, right? Is it a good way, what do you think?

1 Answer 1


To answer the update, the TRUNCATE PARTITION will not reset the auto_increment field. This makes sense since it would be sticky to handle in relation to the other partitions in the auto increment scenario.

However, a table truncate will reset the auto increment key.

  • thank you for your comment. What do you think about my UPD2?
    – Kirzilla
    Commented Sep 12, 2014 at 15:57
  • Yes, that's a fine way to find out the proper partition in this case. Commented Sep 12, 2014 at 16:03
  • TRUNCATE PARTITION will reset the auto_increment key if the auto_increment key belong to this PARTITION.
    – AlSan
    Commented Oct 31, 2018 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.