How to split a MySQL table into a column that is not in a unique index

Question

Suppose there is a simple table of products. Each product has its own unique identifier and category. Users often search by category, so I want to categorize products. Each category in one section, for example

How can I do it? Of course, I have a primary key in the ID column and I need a unique identifier. Not unique in every category.

However, in the partitiong parameter, this restriction means that "every unique key in the table must use every column in the table partition expression."

Well, doesn't that make partitioning a little 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 category field to the current primary key. (Your primary key will remain the primary key)
Then you can break the table into categories.

Here is the code you can use:

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

Add the auto_increment parameter to id if you want it to be truly unique, and do not specify the id value when inserting data into the table. The identifier will be determined by the database server upon insertion.

Change the field names and key names as necessary.

Documentation:
Partition Types
Key partioning