List:General Discussion« Previous MessageNext Message »
From:Jeremy Chase Date:February 1 2013 8:18pm
Subject:Re: InnoDB interaction between secondary and primary keys.
View as plain text  
Thank you Rick!

--
Jeremy Chase
http://twitter.com/jeremychase


On Wed, Jan 30, 2013 at 4:24 PM, Rick James <rjames@stripped> 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@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