0

I have a table having id as primary key and date column.

I want to partition the data.. I felt month would be the best option so that I have 12 partitions.. How do I achieve that !

I am using mysql 5.6 on AWS RDS.

alter table report.track_pdp_visit partition by hash(start_date);

It gives me following error:

Field 'start_date' is of a not allowed type for this type of partitioning

  • 1
    Use a range. Anyway, why would you want to partition a date by hashing it? Typically what people want to do is put current month (quarter, year..i.e heavily updated) somewhere different to the others (fewer or even no updates). Hashing the dates means that this month's data will be scattered among the 12 partitions. – Vérace Feb 3 '16 at 14:33
  • can u suggest me alter script for the same ..thanks – Deepak Feb 3 '16 at 15:14
1

From here, below is an example that range partitions by YEAR - change as appropriate.

Here is the part of the manual that explains how to alter tables and add partitions. You should be able to take it from here.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE);
1

There are many restrictions on PARTITIONs. But also, there are many reasons why they don't provide any benefit.

What do you hope to gain? HASH partitioning does not provide any performance benefit, unless you are doing "table scans" now.

Vérace's suggestion is a way that would pass the syntax issues, but, again, not likely to have performance benefit.

If you need to delete the old data, then DROP PARTITION is an excellent reason for RANGE partitioning. But that is about the only reason. See my blog for further discussion and code for doing the DROP and REORGANIZE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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