From: Dan Nelson
Date: June 15 2009 9:10pm
Subject: Re: The size of an index (INDEX_LENGTH)
List-Archive: http://lists.mysql.com/mysql/217871
Message-Id: <20090615211032.GA79514@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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