MySQL Forums
Forum List  »  Partitioning

Re: Best partitioning strategy VS shortest primary key
Posted by: Rick James
Date: December 24, 2011 03:03PM

- 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Best partitioning strategy VS shortest primary key
2150
December 24, 2011 03:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.