The hint of a change is in here (search for "secondary"): http://jorgenloland.blogspot.co.uk/2012/12/favorite-mysql-56-features-optimizer.html
> -----Original Message----- > From: Rick James > Sent: Wednesday, January 30, 2013 1:08 PM > To: 'Jeremy Chase'; mysql@lists.mysql.com > Subject: RE: InnoDB interaction between secondary and primary keys. > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
