Re: Does Mysql Date Partition can be created on date time for every day???

[Akshath Hegde]

Hi Rick,
Thanks for quick reply.

1. I have table with 50 lakh records (50,00,000). This table will be queried every 15min. But query execution time is more compare, It taking more than 10min. to fetch records.

So if i create partition, whether i will get high performance?

2. Range partition is not dynamic partition, i have to create partition for each date by specifying range in partition.

But in KEY partition just have to specify column name.

EXAMPLE :
CREATE TABLE dummy_table (c1 datetime not null)
PARTITION BY RANGE(TO_DAYS(c1)) (
PARTITION p0 VALUES LESS THAN (-5),
PARTITION p1 VALUES LESS THAN (0),
PARTITION p2 VALUES LESS THAN (100),
PARTITION p3 VALUES LESS THAN (200),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);


KEY Partition :
CREATE TABLE members (
c1 DATETIME NOT NULL
)
PARTITION BY KEY(DATE(c1)) PARTITIONS 366;


So in this case KEY will be better correct?

Please suggest.