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 dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
