MySQL table partitioning on timestamp

3
votes

I have partitioned a table (because of an out of memory error - table got too big). I have partitioned it on a timestamp column as shown below:

CREATE TABLE test (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    dob timestamp NOT NULL
)
PARTITION BY RANGE( unix_timestamp(dob) ) (
    PARTITION p2012 VALUES LESS THAN (unix_timestamp('2013-01-01 00:00:00')),
    PARTITION p2013 VALUES LESS THAN (unix_timestamp('2014-01-01 00:00:00')),
    PARTITION pNew VALUES LESS THAN MAXVALUE
);

I was hoping that the process of partitioning would also help in speeding up a couple of my queries whihc take a few hours to run; however, this type of partitioning doesn't seem to kick in and all partitions are still being used and scanned through for the queries. I have tried, and failed, with a couple more approaches:

1) Tried to use different range for the partitioning

CREATE TABLE t2 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob timestamp NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION pNew VALUES LESS THAN MAXVALUE
);

However, that results in an error: Error Code: 1486. Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

2) Gave up on changing partitioning to be recognized by the query optimizer, and as suggested in MySQL's Doc - 18.5 Partition Selection tried specifying which partitions to use in the select statement instead:

select * from t2 partition (p2012)

But, that returns a syntax error Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p2012) LIMIT 0, 1000' at line 1

Does anybody have any suggestions what else I could try to utilize table partitioning to optimize the queries?

3
mysqlsql
so the partition p2012 really exist in your table? - hashbrown
What is the exact MySQL version you're using? - Mchl
I did a little bit more testing... I tried the same test on a machine with 5.5 installed and the query optimizer does utilize partitioning as expected. Since I'm stuck with 5.1, I'm out of luck. I was trying to avoid this, but I think I will resort to adding an index on the dob column to speed up the queries. - Kate

3 Answers

2
votes

  1. You can do this if you use DATE or DATETIME instead of TIMESTAMP as the data type. 

    CREATE TABLE t2 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATETIME NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION pNew VALUES LESS THAN MAXVALUE
);

  2. Using the partition-selection hint is only supported in MySQL 5.6 and later.

    See http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
    Note that the page of the manual is only for MySQL 5.6. If you try to click on MySQL 5.5 documentation link, it redirects you back to 5.6.

1
votes

You can use UNIX_TIMESTAMP() function. Example from MySQL docs:

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)

);

You can find it in: https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html.

-1
votes

The wrong is that, This field dob is not unique key!

you can use this command:

  CREATE TABLE t2 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob timestamp NOT NULL,
    unique 'dob' (dob)
 )

or the table is exist:

alter table t2 add UNIQUE(dob)

You can try it!