Re: InnoDB interaction between secondary and primary keys.
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
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
RE: InnoDB interaction between secondary and primary keys.
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
