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!

This is a really good article on partitions in MySQL / MariaDB. Maybe this already answers everything for people coming here: "Don't use PARTITION unless you will have >1M rows" https://mariadb.com/kb/en/library/partition-maintenance/ — Christopher K., Aug 14 '18 at 08:22

score 28 · Answer 1 · edited May 15 '18 at 16:08

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';

I'm learning about partitions, how come the use of `DATE_FORMAT()` isn't required in the `WHERE` statement? — enchance, Aug 10 '15 at 12:14
@Steyx - Perhaps you meant `WHERE ftime >= '2011-04-03' AND ftime < '2011-04-03' + INTERVAL 1 DAY`. — Rick James, Nov 26 '15 at 04:31
`PARTITION BY RANGE( TO_DAYS(datetime) )` should be `PARTITION BY RANGE( TO_DAYS(ftime) )` . please correct and update it. Thankyou — Jay Zamsol, Mar 28 '18 at 09:45
according to the docs you linked it seems to me that pruning is done if TO_DAYS(), right? "Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. Pruning can also be applied for such tables when the partitioning expression uses the TO_SECONDS() function" — Vlad, Aug 04 '20 at 20:36

score 11 · Answer 2 · answered May 28 '11 at 18:39

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..

`^1` what if number of years in table are unknown, whether `PARTITION BY hash (YEAR(ftime))` will work ? — Akshay Hegde, Jan 25 '17 at 10:18

score 2 · Answer 3 · answered Oct 25 '11 at 21:40

I just recently read a MySQL blog post relating to this, at http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html.

Versions earlier than 5.1 required special gymnastics in order to do partitioning based on dates. The link above discusses it and shows examples.

Versions 5.5 and later allowed you to do direct partitioning using non-numeric values such as dates and strings.

Rick James · Answer 4 · 2022-09-14T04:17:37.420

1

Don't use CHAR, use VARCHAR. That will save a lot of space, hence decrease I/O, hence speed up queries. (Exception: If the column is really fixed length, then use CHAR. And it will probably be CHARACTER SET ascii.)

reporterip: (46) is unnecessarily big for an IP address, even IPv6. See My blog for further discussion, including how to shrink it to 16 bytes.

PARTITION BY RANGE(TO_DAYS(...)) as @Steyx suggested, but don't have more than about 50 partitions. The more partitions you have, the slower queries get, in spite of the "pruning". HASH partitioning is essentially useless.

More discussion of partitioning, especially the type you are looking at. That includes code for a sliding set of partitions over time.

edited Sep 14 '22 at 04:17

answered Nov 26 '15 at 04:39

Rick James

135,179
13
127
222

1

"Don't use CHAR, use VARCHAR" seems a bad advice as a general rule and especially in this case. If all `logid` values have 16 bytes, then `CHAR(16)` needs 16 bytes and `VARCHAR(16)` needs 17 bytes. Plus, index lookups are reported to be faster on `CHAR`. And this has nothing to do with the actual question (partitions). For `CHAR` vs `VARCHAR`, see https://dba.stackexchange.com/questions/2640/what-is-the-performance-impact-of-using-char-vs-varchar-on-a-fixed-size-field – Christopher K. Aug 14 '18 at 08:13
@ChristopherK. - if `logid` is always 16 byte, then, sure, use `CHAR(16)`. But probably also say `CHARACTER SET ascii`. – Rick James Aug 16 '18 at 05:03
True. `CHAR(16)` can be larger than 16 bytes for multibyte charsets, but the same holds for `VARCHAR(16)`. This is all discussed in the question I linked. I just wanted to make clear that "Don't use CHAR, use VARCHAR" is not a good advise in general. – Christopher K. Aug 17 '18 at 06:43
`CHAR(16)` -- if ascii will take 16 bytes; if utf8mb4 will take 64 bytes, even if only ascii characters are stored in it. – Rick James Aug 17 '18 at 07:02

how to partition a table by datetime column?

4 Answers4

Linked