Partitioning with primary key gives Error ERROR 1503 (HY000)

0
votes

It may sound similar but,I am working on partitioning on some table...the table looks like

mysql> DESC SHOPS; +-------------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+-------------------+-----------------------------+ | SHOP_ID | int(255) | NO | PRI | NULL | | | SHOP_NAME | varchar(50) | YES | | NULL | | | SHOP_CREATED_DATE | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------------+-------------+------+-----+-------------------+-----------------------------+ 3 rows in set (0.00 sec)

so i have search feature where people can search only by shop name so table have around 1 million records so i wanted to RANGE partitioning on shop name alphabetically but i cant do since i have primary key shop_id and shop name can be same...and getting error

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Solution:

ALTER TABLE SHOPS ADD CONSTRAINT T  UNIQUE (SHOP_ID,SHOP_NAME);

And do partitioning ...i cant do this because it does not make sure shop_id is unique(Primary Key)

1
mysqlpartitioning
Read this part of the documentation: dev.mysql.com/doc/refman/5.7/en/…. In particular, "every unique key [and primary key] on the table must use every column in the table's partitioning expression" - Gordon Linoff
So i cant do partition on shops table...???is there any alternative to make sure shop id unique - gopal gupta

1 Answers

0
votes

You can, and you must. Assuming you always let AUTO_INCREMENT do its thing, shop_id will always be unique, and any index starting with shop_id is all you need.

int(255) -- The (255) means nothing. An INT (SIGNED, by default) has a range of -2 billion to +2 billion and occupies 4 bytes, regardless of the (...) after it..

There is probably no performance advantage (or any other advantage) of Partitioning this table. If you think otherwise, please show us a query that you think will benefit.

Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.