List:General Discussion« Previous MessageNext Message »
From:Rick James Date:January 30 2013 9:24pm
Subject:RE: InnoDB interaction between secondary and primary keys.
View as plain text  
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@stripped
> 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:jeremychase@stripped]
> > Sent: Wednesday, January 30, 2013 11:25 AM
> > To: mysql@stripped
> > 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
Thread
InnoDB interaction between secondary and primary keys.Jeremy Chase30 Jan
  • RE: InnoDB interaction between secondary and primary keys.Rick James30 Jan
  • RE: InnoDB interaction between secondary and primary keys.Rick James30 Jan
    • Re: InnoDB interaction between secondary and primary keys.Jeremy Chase1 Feb