I have a table with PRIMARY KEY (`id`) and I want to change it to PRIMARY KEY (`username`, `id`). These columns are defined as:

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',

This table is within a primary/secondary MySQL topology with binary row replication. Can I get away with taking the replica offline, changing the primary key, and reconnect it to the source without changing the source? For clarity, only the primary key index would be different between the source/replica. All other columns/order of columns would be the same.

  • 1
    Easier to just have an additional index on the slave?
    – Philᵀᴹ
    Apr 18, 2019 at 14:08
  • @Philᵀᴹ, the idea here is that i would promote the slave, and then upgrade the old master to have the same primary key. My goal is to reduce I/O required when selecting a group of "messages" for a single "username". A composite primary key should cluster sequential rows for each user rather than having them randomly spread out based on the primary ID Apr 18, 2019 at 19:07
  • What is the value of binlog_format? Do you need id for anything? I approve of the ultimate goal as a performance boost; I have seen it help. But we need more info to look for gotchas. You would have to add INDEX(id) to keep AUTO_INCREMENT happy. Is there something else that could pair with username to be unique? How many indexes? Is it InnoDB? Show us the other secondary keys; they may need changing.
    – Rick James
    Apr 26, 2019 at 22:26

1 Answer 1


Depends on the Storage Engine


The auto_increment column will behave but the Replica will become very bloated. Why ???

For any given non-unique index in an InnoDB table, each entry in s secondary index will have a copy of the PRIMARY KEY as part of the entry.

If your InnoDB table has PRIMARY KEY (id) (which is 8 bytes), then each secondary index entry will have the id attached.

Now if you change the PRIMARY KEY to (username,id), then each secondary index entry will have the (username,id) attached. That can be up to 28 (8 + 20) bytes to each entry.

This will make INSERTs slower on the Replica.

This also allow duplicates of username.

You are better off creating a UNIQUE KEY on username.



Any MyISAM table that has a compound key that involves an auto_increment column with increment based on the presences of the other columns

EXAMPLE from the MySQL Documentation

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
INSERT INTO animals (grp,name) VALUES
SELECT * FROM animals ORDER BY grp,id;

which prints out

| grp    | id | name    |
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |

Do you see how id increments on a common value when grp already exists in the table ?

The Source and the Replica will be so horribly out-of-sync. There will be no way to know which row in the Source belongs to a row in the Replica, and vice versa.

This makes changing the PRIMARY KEY of a MyISAM table totally dangerous for a Replica.

I have discussed this subject over 6 years ago

UPDATE 2019-04-20 08:52 EDT

Of course, it is possible but BEWARE !!! : My post was just about the dangers of doing so. In the event of problems, MyISAM would be an absolute nightmare to unravel. InnoDB would be somewhat OK but you are messing with the PRIMARY KEY.

Back on Aug 14, 2012, I have recommended changing the storage engine and adding an index on a replica (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?). I still recommend do something like this for older versions of MySQL (When FULLTEXT indexes for InnoDB did not exist yet). The PRIMARY KEY can be a different story.

I can easily see the EXPLAIN plan of a SELECT being radically different on a replica from the EXPLAIN plan of its source given a completely different order of PRIMARY KEY columns.

You could never use a tool like pt-table-checksum and pt-table-sync to reconcile differences should the table become out of sync or replication breaking for unrelated reasons. Why ??? Both tools rely on the PRIMARY KEY columns. Even with plain MySQL, just running CHECKSUM TABLE table_name; would always be different between Source and Replica.

Your hoping for better performance runs the risk of MySQL interpreting the table as not being the same (because it simply won't be the same).

If you looking for the username to be unique, FORGET IT !!! Having (username,id) as a PRIMARY KEY allows for it. If you already have a UNIQUE KEY on username on the Source, then the Replica already has the key it needs.


Make sure the Source has a UNIQUE KEY on the username.

Don't do anything to the PRIMARY KEY on the Replica. If you want to the Source to have it, then do it on the Source so it transfers to the Replica.


Giving you a simple it's possible or it's not possible answer would be absolutely unfair to you given the bullet-riddled problems it would produce down the road for anyone who tries.

I would never recommend changing the PRIMARY KEY of a Replica regardless of storage engine or DBMS. If you are still willing to accept the responsibility of changing the PRIMARY KEY on a Replica after reading this, Godspeed, Spiderman !!!

  • Thanks for the post but it’s still not clear whether it is possible or not. Also, username is not unique in this table, but it is indexed. So insert performance would be slower, as rows would be inserted all over the primary index rather than always at the end. But I can about read performance, and minimizing iops required for reading sequential rows per username. Apr 20, 2019 at 0:32
  • @ChrisLeavoy - Insert performance would not be that much, if any, worse -- You probably have a few hundred "active" users; that would lead to a few hundred "hot spots" in the table. That is not really worse than appending everything at the 'end' due to auto_inc.
    – Rick James
    Apr 26, 2019 at 22:31
  • @ChrisLeavoy - Yes, read performance with be better for WHERE username='...' AND ... when it returns multiple rows and the table is too big to be cached in RAM. How big (GB) is the table? What is innodb_buffer_pool_size? How much RAM?
    – Rick James
    Apr 26, 2019 at 22:32
  • @RickJames, we're looking at a fairly sizable table in the 60gb range, and a million of active users. Apr 27, 2019 at 15:44
  • 1
    @ChrisLeavoy - Change the PK now, before you get to a million users!
    – Rick James
    Apr 27, 2019 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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