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
http://dev.mysql.com/doc/refman/5.1/en/key-space.html
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.
http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html
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.
--
Dan Nelson
dnelson@stripped
