Thank you Rick! -- Jeremy Chase http://twitter.com/jeremychase
On Wed, Jan 30, 2013 at 4:24 PM, Rick James <rja...@yahoo-inc.com> wrote: > 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 >
