9

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

Josef Sábl
  • 7,538
  • 9
  • 54
  • 66

1 Answers1

13

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

Jocelyn
  • 11,209
  • 10
  • 43
  • 60
  • Sure, but id will then not be unique but only unique in category, which I don't want. Replace Into will stop working then. In case category of product will change the product would be duplicated. But anyway, thanks for your answer. – Josef Sábl Aug 02 '12 at 07:57
  • No. Adding a field to a primary key can't make it non_unique, as I have written in my answer. Why don't you just try and see that it works? I have used that partitionning on a big log table (between 2 and 5 million records) to partition by day: it allows very fast `SELECT` by day, and extremely fast deletion of all records for a particular day (by just dropping a partition). – Jocelyn Aug 02 '12 at 08:49
  • Oh no, I know PRIMARY KEY will still be unique. But ID which will now only be part of the key will stop being unique as different value in category column can make the key unique although ID will be same. But it looks like MySQL just works like that (although it doesn't make much sense) and I will have to live with it. Anyway, thanks for your effort. – Josef Sábl Aug 03 '12 at 08:52
  • Just try it before making false assumptions! Make a full copy of your table and your data, then run the 2 queries in my answer. You will see that everything works. Adding a field to a primary key will NEVER make it non-unique (yes, NEVER!). Adding a field to the primary key will not prevent the ID from being unique, it was already unique and will remain unique. Just try and you will see all I wrote is true and works as explained. If you prefer, create a copy of your table and data, and test partitioning on this copy. – Jocelyn Aug 03 '12 at 14:13
  • I added documentation links to my answer above. – Jocelyn Aug 03 '12 at 14:16
  • I know the key will still be unique, I am talking about ID, which is only part of the key now. To illustrate it better, let's have a table of two columns ```(id, category)```. Key is ```PRIMARY KEY (id, category)```. Then you run two queries: ```INSERT (1, 1)``` and ```INSERT (1,2)```. You will have two rows (1, 1), (1, 2) and there will be two rows with ```id = 1``` => ```id``` is not unique. Although I am pretty sure I made a test, here are results: http://screenpresso.com/=772qg http://screenpresso.com/=JDIXc – Josef Sábl Aug 06 '12 at 11:01
  • Your two queries will pass, true. But they will break my application as it uses REPLACE when changing properties of product (e.g. change products category). And in that case, the product gets duplicated into each category. – Josef Sábl Aug 06 '12 at 11:09
  • Add 'auto_increment' 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. – Jocelyn Aug 06 '12 at 14:55
  • Not applicable in my situation. Let's say data are imported from external source along with ID's. But never mind, I will manage somehow. Thanks for your effort :-) The point for me is that when you want to use partitioning, you have to change application itself and resulting schema might not be very tidy and intuitive (understand workarounds). – Josef Sábl Aug 07 '12 at 15:11
  • Why does it matter to you that the id is not unique in your table? The primary key is (`id` , `category`), not (`id`) – Jocelyn Aug 07 '12 at 15:13
  • There are many reasons. Imagine for example you wish to join this table of products to let's say: history o prices of the product. The second table with the history will have to have (id, category) as key as well. And what happens when product changes category? – Josef Sábl Aug 07 '12 at 15:18
  • Simply put: The schema will be different than business logic which can only lead to disaster. – Josef Sábl Aug 07 '12 at 15:19
  • Yes, I can, but I can´t use partitioning then :) Pretty much point of this discussion. – Josef Sábl Oct 17 '13 at 12:57
  • @JosefSabl you can, but you must use only columns that make up the unique index (which happens to be just ID) – prusswan Mar 16 '16 at 10:18
  • @prusswan And that means that I will not have the table partitioned by category which is the point of this question. – Josef Sábl Mar 18 '16 at 12:21
  • I'm trying to do a similar thing where I have a table of tokens and related data that I wish to range partition by the week of the year. Each week a new partition is created and the oldest is dropped. The token field needs to be unique and not auto increment. When I apply partitioning I can insert duplicate tokens across different weeks. This seems to be a bit of an oversight by MySQL. The only thing I can think of is to prefix the token with the year and week. – Jason Royle Jul 26 '16 at 10:55
  • I have ` A PRIMARY KEY must include all columns in the table's partitioning function` After Trying This Method!!! – Mahmoud.Eskandari Oct 30 '16 at 17:57