Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column):

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE ( to_days(end_time))
(PARTITION p20130521 VALUES LESS THAN (735375) ENGINE = InnoDB,
....
PARTITION p20130603 VALUES LESS THAN (735388) ENGINE = InnoDB,
PARTITION p20130604 VALUES LESS THAN (735389) ENGINE = InnoDB,
PARTITION p20130605 VALUES LESS THAN (735390) ENGINE = InnoDB,
PARTITION p20130606 VALUES LESS THAN (735391) ENGINE = InnoDB,
PARTITION p99991230 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Starting with MySQL 5.5, it is now possible to avoid the to_days and partition by real dates. Is there any (speed) advantage to doing so?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Using to_days to Partition

The to_days function yields a 6 digit number in the 700,000's (in the MEDUIMINT range).

Using DATE or DATETIME to Partition

DATE takes up 3 bytes. DATETIME takes up 8 bytes.

Conjecture

UNIX_TIMESTAMP of today's date exceeds 1.37 billion. It takes 4 bytes to store that. Range partitioning allows for range values that high. Here is an example of that from the MySQL Documentation:

It is also possible to partition a table by RANGE, based on the value of a TIMESTAMP column, using the UNIX_TIMESTAMP() function, as shown in this example:

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

Partitions seem to be numerically represented as a 4-byte unsigned integer. As mentioned, DATE takes up 3 bytes, but, from what I have observed, partitions are bounded by 4-byte numbers. Given this, expressing a DATE as a RANGE parameter really does not buy you anything except cleaner syntax for defining the range.

Expressing to_days of a DATE yourself or having mysql do it for you boils down to personal choice.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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