How to partition MySQL table by column that is not in the unique index

Question

Let's have a simple table of products. Each produch has its unique ID and category. Users often search by category so I want to partition products by category. Each category in one partition e.g.

How do I do it? Because of course I have a primary key on my ID column and need my ID unique. Not unique in each category.

However partitiong has this limitation that "every unique key on the table must use every column in the table's partitioning expression".

Well, doesn't this make partitioning a bit useless? Or am I missing something? What should I do?

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html

Answer 1

The trick is to add the field category to your current primary key. (Your primary key will remain a primary key)
Then you can partition your table by category.

Here is the code you may use:

ALTER TABLE `products` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id` , `category` );
ALTER TABLE `products` PARTITION BY KEY(category) PARTITIONS 6;

Add auto_increment option to the id if you want it to be really unique, and don't specify the id value when you insert data in the table. The id will be determined by the database server upon insertion.

Change field names and key names if necessary.

Documentation:
Partitioning types
KEY Partioning