0

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 master/slave MySQL topology with binary row replication. Can I get away with taking the slave offline, changing the primary key, and reconnect it to the master without changing the master? For clarity, only the primary key index would be different between the master/slave. All other columns/order of columns would be the same.

  • 1
    Easier to just have an additional index on the slave? – Philᵀᴹ Apr 18 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 – Chris Leavoy Apr 18 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 at 22:26
0

Depends on the Storage Engine

InnoDB

The auto_increment column will behave but the Slave 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 Slave.

This also allow duplicates of username.

You are better off creating a UNIQUE KEY on username.

MyISAM

For those still using MyISAM, PLEASE DO NOT ATTEMPT THIS UNDER ANY CIRCUMSTANCES !!! Why ???

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,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');
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 Master and the Slave will be so horribly out-of-sync. There will be no way to know which row in the Master belongs to a row in the Slave , and vice versa.

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

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 Slave (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 Slave from the EXPLAIN plan of its Master 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 Master and Slave.

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 Master, then the Slave already has the key it needs.

SUGGESTION

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

Don't do anything to the PRIMARY KEY on the Slave. If you want to the Slave to have it, then do it on the Master so it replicates to the Slave.

EPILOGUE

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 Slave regardless of storage engine or DBMS. If you are still willing to accept the responsibility of changing the PRIMARY KEY on a Slave 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. – Chris Leavoy Apr 20 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 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 at 22:32
  • @RickJames, we're looking at a fairly sizable table in the 60gb range, and a million of active users. – Chris Leavoy Apr 27 at 15:44
  • @ChrisLeavoy - Change the PK now, before you get to a million users! – Rick James Apr 27 at 16:48

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.