RE: index & innodb

[Rick James]

1.  Secondary indexes (but not the PRIMARY KEY) requires additional disk space.  A
secondary index implicitly includes copies the field(s) of the PRIMARY 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, one 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 is in ibdata1. 
Probably the identical amount of space either way.

file_per_table is almost always the better way to go.  However, it is awkward to convert a
big existing system, since it is not easy to free up the space 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
> 
> 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.
> 
> --
> 3murthy
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql