MySQL Partitioning - Error by Primary key and Unique records

Question

i've a large table with about 2Mln records, and i want to partition it.

I've the id column as PRIMARY AUTO_INCREMENT int (and it must to be always UNIQUE). I've a column "theyear" int(4) and i want to partition BY RANGE from 2016 to 2050, because most of Query use a WHERE statement getting 1 year at time.

Making the partitioning i get an error saying that the "theyear" key must be with PRIMARY KEY, so i've edited the primary key doing a multicolumn key PRIMARY (id, theyear).

It's all OK, but my "id" columns isn't UNIQUE anymore, because it checks "theyear" columns too... So if I insert:

INSERT INTO table (id, theyear) VALUES (1, 2016);
INSERT INTO table (id, theyear) VALUES (1, 2017);

it says NO ERROR, because the UNIQUE check both id and theyear.

How can implement partitioning without lose UNIQUE on "id" column?

Thanks.

Have you tried to add a separate unique constraint against year? — cwallenpoole
I am basing this on the documentation: dev.mysql.com/doc/refman/5.7/en/partitioning-range.html, but it seems to me that you don't need to change your primary key for a RANGE partition. All the example they use don't have a PRIMARY KEY. Am I missing something? Are you trying to partition by KEY() by mistake? — Jacques Amar
Why do you want to partition? What query do you think will run faster? Please provide that query and SHOW CREATE TABLE. I will probably argue that a suitable index will as fast or faster then partitioning. Perhaps the index would start with YEAR. Hint: mysql.rjweb.org/doc.php/partitionmaint — Rick James
@RickJames i wanna use partitioning because my table is: 13,880,195 records (5.1 GiB) right now ... — StefanoV827
@JacquesAmar i'm trying to use KEY() but i get: "#1503 - A PRIMARY KEY must include all columns in the table's partitioning function" — StefanoV827

Jacques Amar Jacques Amar · Accepted Answer · 2017-09-13T16:27:30

Since your queries WILL use theyear in it, partitioning by RANGE will help with large data, with the proper INDEX.

Don't use KEY() partitionning, keep your existing primary key

INDEX IDX_theyear(theyear)

and

PARTITION BY RANGE (theyear) ( 
PARTITION p0 VALUES LESS THAN (2017), 
PARTITION p1 VALUES LESS THAN (2018), 
PARTITION p2 VALUES LESS THAN (2019), 
PARTITION p3 VALUES LESS THAN (2020)

for example. You can add more partitions later, but harder. If your data is older, then obviously start with the oldest year

MySQL Partitioning - Error by Primary key and Unique records

1 Answers