Best partitioning strategy VS shortest primary key

[Jérémy Cohen Solal]

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