In the last episode (Jun 15), Morten said:
> I dropped an index on a table with 25M records today. The INDEX_LENGTH
> in information_schema.tables shrank from 3834642432 to 3215982592, ie.
> ~618Mb difference
> The index was on an int(11) column.
> That means each index key takes up ~618Mb/25M ~= 25 bytes but that
> doesn't sound right? Is that true, or is information_schema.tables
> unreliable or?
Innodb or MyISAM? According to
a MyISAM index should be around 25M*(4+8)/.67=450 MB, quite a bit smaller
than your delta. Innodb, however, gets closer to your number.
Each row in a secondary index contains the keys in that index, plus the
primary keys, and given random insertion (which is common for secondary
keys) index pages between 1/2 and 15/16ths full. So if your primary key is
also a NOT NULL INT, the index should be 25M*16 = 400MB worth of data plus
between 25 and 200MB of slack space. 600 is at the very top end of that
range, so I think your primary key is larger than a plain INT.