Re: Best partitioning strategy VS shortest primary key

[Jérémy Cohen Solal]

One thing I forgot to explain is that 90% of the requests on these table will be done on current year and last year. But I still need to keep (at least for 4-5 years) the data in database. (the column date will be used with each requests)

This is why I look at MySQL partitions.


> What is the best schema creation practice for this case
CREATE TABLE ... ?? (What does your question mean?)

No I mean what is the best way between theses alternatives:

- PARTITION BY RANGE YEAR(DATE) without PRIMARY KEY

- PARTITION BY RANGE YEAR(DATE) with PRIMARY KEY (ID, DATE)

- PARTITION BY RANGE YEAR(DATE) with PRIMARY KEY (ID) + UNIQUE(DATE, ID)

- Another one?