Mysql – Efficient way to DELETE whole partition’s data in table partitioned by HASH(DAY(datetime))

MySQLpartitioning

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?

Best Answer

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.

Related Solutions

Mysql – How to partition a table by timestamp, where data from every i’th day goes to partition i

You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression. But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql> create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql> insert into table_to_partition values (unix_timestamp(now()));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql> select table_name, partition_name, table_rows 
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Related Question