MySQL Forums
Forum List  »  Partitioning

Best partitioning strategy VS shortest primary key
Posted by: Jérémy Cohen Solal
Date: December 19, 2011 06:12AM

Hello,

I am wondering something with MySQL partitioning.

I know it is a really best practice to define the primary key of a table as small as possible.

But suppose I have a table like that (OLTP example database)

CREATE TABLE `orders` (
`order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`client_id` mediumint(8) unsigned NOT NULL,
`amount` smallint(5) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`order_id`),
KEY `client_id` (`client_id`),
KEY `date` (`date`),
) ENGINE=InnoDB


I want to partition this table by year with YEAR(date)

Here are my questions:

Partitioning by DATE implies that I add the field 'date' to the primary key (ex: PRIMAY(order_id, date)). This is the opposite to the best practice 'keep the PK small'.

1) Will the join with every records depending of this table (ex: a table orders_logs with a order_id FK) will have poorer performance because of the primary key size?

2) What is the best schema creation practice for this case ?

3) Is it a general rule of adding the 'date' column to the primary key of any table I want to partition by YEAR(date) ?

Thank
Jeremy

Options: ReplyQuote


Subject
Views
Written By
Posted
Best partitioning strategy VS shortest primary key
4258
December 19, 2011 06:12AM


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.