MySQL Forums
Forum List  »  Partitioning

Re: Best partitioning strategy VS shortest primary key
Posted by: Rick James
Date: December 20, 2011 10:31AM

INT is 4 bytes; DATE is 3. A 7-byte PK is not that big.

Why PARTITION? What benefit do you hope to gain? (There are a few possibilities, plus a few "reasons" that do not work out.)

> Will the join with ...
ON what? If you are joining on only order_id, then there is very little difference.

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

> Is it a general rule of adding the 'date' column to the primary key of any table I want to partition by YEAR(date)
If the table is PARTITIONed on YEAR(date), ...
If you include a date range or YEAR(date) in the WHERE clause, then it can (perhaps) reach directly into the correct PARTITION.
If you don't, then it has to look in all partitions; this takes longer.

If you are joining (or simply SELECTing) on order_id (the first or only field of the PK), with or without date, a non-partitioned table will be at least as fast as a partitioned table.

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

Options: ReplyQuote

Written By
Re: Best partitioning strategy VS shortest primary key
December 20, 2011 10:31AM

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.