secondarykey and redundantkey are redundant with each other -- in all versions of InnoDB.
One "expert" said that redundant key would have two copies of `1`,`2`. I think he is wrong. I believe the two are the same in size. There is a subtle change in 5.6 that _may_ make a _few_ queries work better with redundantkey. I prefer to specify as many fields in the key as make sense for the SELECT(s), then let the engine add any more fields as needed to fill out the PK. That is, I might say (3,4) or (3,4,1) or (3,4,1,2) or (3,4,2), etc, depending on the queries. When looking up a row(s) by a secondary key, the engine first drills down the secondary BTree, finds the PK(s), then drills down the PRIMARY BTree. > -----Original Message----- > From: Jeremy Chase [mailto:jeremych...@gmail.com] > Sent: Wednesday, January 30, 2013 11:25 AM > To: mysql@lists.mysql.com > Subject: InnoDB interaction between secondary and primary keys. > > Hello, > > I've been working with a secondary index and would like some > clarification about how the primary columns are included. So, in the > following example, is the secondaryKey effectively the same as > redundantKey? > > CREATE TABLE `example` ( > `1` int(10) unsigned NOT NULL, > `2` int(10) unsigned NOT NULL, > `3` int(10) unsigned NOT NULL, > `4` int(10) unsigned NOT NULL, > PRIMARY KEY (`1`,`2`), > KEY `secondaryKey` (`3`, `4`) > KEY `redundantKey` (`3`, `4`, `1`, `2`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > Background: http://dev.mysql.com/doc/refman/5.5/en/innodb-index- > types.html > > "In InnoDB, each record in a secondary index contains the primary key > columns for the row, as well as the columns specified for the secondary > index." > > Thank you! > Jeremy
