From: Rick James
Date: October 31 2012 6:53pm
Subject: RE: index & innodb
List-Archive: http://lists.mysql.com/mysql/228552
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB1490E7A1F2@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

1.  Secondary indexes (but not the PRIMARY KEY) requires additional disk sp=
ace.  A secondary index implicitly includes copies the field(s) of the PRIM=
ARY KEY; this is how it can get to the actual data row.  Finding a row via =
a secondary key involves two BTree lookups -- one in the secondary index, o=
ne in the primary key index (which has the data with it).

2.  With file_per_table, that is in the .ibd; without file_per_table that i=
s in ibdata1.  Probably the identical amount of space either way.

file_per_table is almost always the better way to go.  However, it is awkwa=
rd to convert a big existing system, since it is not easy to free up the sp=
ace already taken by ibdata1.


> -----Original Message-----
> From: Pothanaboyina Trimurthy [mailto:skd.trimurthy@stripped]
> Sent: Tuesday, October 30, 2012 6:35 AM
> To: mysql@stripped
> Subject: index & innodb
>=20
> hi lists
>           1. does the indexes require additional storage other than the
> table space storage.
>           2. is there any performance difference will be there, if we go
> for innodb_file_per_table.
>=20
> --
> 3murthy
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql