2024年3月12日发(作者：)

mysqltimestamp分区,时间戳上的MySQL表分区

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?