hude index increase on VARCHAR columns with 5.0.3+

[Miha Verlic]

Hello,

starting with 5.0.3, INDEXes on VARCHAR column almost doubled (in my case). There is an incompatible change logged in changelog for 5.0.3:

A VARCHAR column can now contain up to 65535 bytes. In addition, VARCHAR columns now remember trailing spaces.

I'm not sure why this change would affect INDEX size though. It's quite easy to replicate the problem.

1.) Create table with varchar(200) and index on this field:

CREATE TABLE `TEST` (
`XXX` varchar(200) default NULL,
KEY `URL` (`URL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=3300;

2.) Fill table with random data - in my case about 4 million random records with average row size about 80 chars. All records are without any trailing whitespaces.

With MySQL-4.1.x and up to 5.0.2 INDEX size is 172MB. On 5.0.3+ size jumps up to whopping 308MB. If I convert field to char(200) INDEX is 162MB on all versions 4.1.x, 5.0.2, 5.0.3+ (but it does however enlarge data file by 2+ times).

It looks like INDEX size on VARCHAR takes up whole field, not only populated part of the field. Is this a bug, or just new "feature"?