Re: Best partitioning strategy VS shortest primary key

[Rick James]

- PARTITION BY RANGE YEAR(DATE) without PRIMARY KEY
Why have an order_id if it is not unique?

- PARTITION BY RANGE YEAR(DATE) with PRIMARY KEY (ID, DATE)
This might be the best.

- PARTITION BY RANGE YEAR(DATE) with PRIMARY KEY (ID) + UNIQUE(DATE, ID)
No. A PK is a uniqueness constraint; the UNIQUEness constraint of other key adds nothing except overhead.

WHERE order_id = 123
PARTITIONing adds nothing; in fact is slows things down a little.

WHERE client_id = 234 AND YEAR(date) >= 2010
might use the PARTITIONing. But non-PARTITIONing, plus
INDEX(client_id, date)
WHERE client_id = 234 AND date >= '2010-01-01'
would be more efficient than any flavor of PARTITIONing.

For the yearly maintenance:
DROP PARTITION yr2006;
ALTER TABLE REORGANIZE PARTITION future
yr2012 VALUES LESS THAN '2013-01-01',
future VALUES LESS THAN MAXVALUE;
is much more efficient than
DELETE FROM tbl WHERE date < '2007-01-01';

So, I repeat... Please provide the typical SELECT statements. It is hard to make judgement calls without knowing such details.