0

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,  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY HASH(DAY(`dateTime`)) PARTITIONS 31

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?

3

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 Sep 12 '14 at 15:57
  • Yes, that's a fine way to find out the proper partition in this case. – LowlyDBA Sep 12 '14 at 16:03
  • TRUNCATE PARTITION will reset the auto_increment key if the auto_increment key belong to this PARTITION. – AlSan Oct 31 '18 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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