Re: Huge index file, why?

[Michael Widenius]

>>>>> "Scott" == Scott Hess <scott@stripped> writes:

Scott> I've done some experimenting.  Having it be varchar(32) versus varchar(22)
Scott> makes no difference in ISM size, if none of the values have more than 22
Scott> characters.  It doesn't make a difference what combination of altering the
Scott> column, dropping the index, adding the index, or whatever I do.  This is
Scott> probably accounted for by MYSQL's indexing of prefixes, somehow.

This should be right.

You can check the index types by doing:

(my)isamchk --dvv index_file_name

Scott> I've also experimented with converting the varchar to char, and it didn't
Scott> make any difference.

Scott> Doing isamchk -r _does_ result in a smaller ISM file.  I'm betting it packs
Scott> the keys differently than mysqld does, resulting in an ISM file that's
Scott> smaller.

Yes;  In this case all key blocks will be filled to 100% (In normal
usage the key blocks will 'only' be filled to about 70 %).

Note that if you only want to pack the indexes, you should run:

isamchk -rq -Si table_name

(The -Si sorts the index block, which should give you a little better
access times)

Regards,
Monty