I have a table that has a primary key which does not include an auto_incrementing number. The table needs to be partitioned (on that primary key) for the overall strategy of reducing contention points.

I need to add an auto_incrementing number outside the primary key/partitions, but I am concerned about how MySQL will be able to keep the right sequenced number, if under the hood, all partitioned tables are in essence different tables and as that auto_incrementing number cannot be in a separate unique index.

So the question is, will I get consistency issues with the sequence doing this or does MySQL have a way to keep it accurate?

  • 1
    or does MySQL have a way to keep it accurate? Yes. auto_incrementing number cannot be in a separate unique index It does not need in UNIQUE index. Any index where AI field is the first one (in MyISAM - may be second) is enough. And all partition indices are processed as one single index in that process. Anycase you may test... – Akina Oct 9 '19 at 10:14

Rarely does an app need the constraint about an AUTO_INCREMENT being UNIQUE. Uniqueness is not guaranteed.

Sometimes it is best to turn

PRIMARY KEY(id),  -- (id is auto_increment)


PRIMARY KEY(foo, id),

The purpose is to cluster the rows based on foo because most of the access is done with WHERE foo = constant.

The INDEX(id) is all that AUTO_INCREMENT requires. It does not require uniqueness; it does not require PRIMARY KEY.

I added id to the PK on the assumption that foo is not, itself, unique. The combination foo, id is unique (assuming you are not playing games with id), thereby satisfying the uniqueness requirement of PRIMARY KEY.

Back to your question -- Sure, go ahead and add the id. It will require

INDEX(id)  or  INDEX(id, ...)

Implementation insight... When a table is opened, the auto_inc value is initialized by doing SELECT MAX(id) FROM tbl. If that table is partitioned, the code already knows how to check MAX(id) from each partition; then it can easily get the overall MAX.

MySQL 8.0 has a slight variation on that -- it saves the auto_inc value somewhere else so that after a server restart, you will not reuse any ids that got lost in what might have been a crash that necessitated rollbacks. DELETEing the last id just before a graceful shutdown is another issue covered.

BTW -- See if you can use the above trick instead of partitioning. I have found very few valid cases for using partitioning. Provide more details on your case; I can probably spot whether it will help or not.

Ref: http://mysql.rjweb.org/doc.php/partitionmaint

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.