how to partition a table by datetime column?

Question

I want to partition a mysql table by datetime column. One day a partition.The create table scripts is like this:

CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY hash (day(ftime)) partitions 31;

But when I select data of some day.It could not locate the partition.The select statement is like this:

explain partitions select * from raw_log_2011_4 where day(ftime) = 30;

when i use another statement,it could locate the partition,but I coluld not select data of some day.

explain partitions select * from raw_log_2011_4 where ftime = '2011-03-30';

Is there anyone tell me How I could select data of some day and make use of partition.Thanks!

Answer 1

Partitions by HASH is a very bad idea with datetime columns, because it cannot use partition pruning. From the MySQL docs:

Pruning can be used only on integer columns of tables partitioned by HASH or KEY. For example, this query on table t4 cannot use pruning because dob is a DATE column:

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

However, if the table stores year values in an INT column, then a query having WHERE year_col >= 2001 AND year_col <= 2005 can be pruned.

So you can store the value of TO_DAYS(DATE()) in an extra INTEGER column to use pruning.

Another option is to use RANGE partitioning:

CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
  PARTITION BY RANGE( TO_DAYS(ftime) ) (
    PARTITION p20110401 VALUES LESS THAN (TO_DAYS('2011-04-02')),
    PARTITION p20110402 VALUES LESS THAN (TO_DAYS('2011-04-03')),
    PARTITION p20110403 VALUES LESS THAN (TO_DAYS('2011-04-04')),
    PARTITION p20110404 VALUES LESS THAN (TO_DAYS('2011-04-05')),
    ...
    PARTITION p20110426 VALUES LESS THAN (TO_DAYS('2011-04-27')),
    PARTITION p20110427 VALUES LESS THAN (TO_DAYS('2011-04-28')),
    PARTITION p20110428 VALUES LESS THAN (TO_DAYS('2011-04-29')),
    PARTITION p20110429 VALUES LESS THAN (TO_DAYS('2011-04-30')),
    PARTITION future VALUES LESS THAN MAXVALUE
  );

Now the following query will only use partition p20110403:

SELECT * FROM raw_log_2011_4 WHERE ftime = '2011-04-03';

Answer 2

Hi You are doing the wrong partition in definition of the table the table definition would like this:

CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY hash (TO_DAYS(ftime)) partitions 31;

And your select command would be:

explain partitions 
    select * from raw_log_2011_4 where TO_DAYS(ftime) = '2011-03-30';

The above command would select all the date required, as if you use the TO_DAYS command as

mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('2007-10-07');
        -> 733321

Why to use the TO_DAYS AS The MySQL optimizer will recognize two date-based functions for partition pruning purposes: 1.TO_DAYS() 2.YEAR()

and this would solve your problem..