Re: Huge index file, why?

[Michael Widenius]

>>>>> "Jules" == Jules Bean <jmlb2@stripped> writes:

Jules> Benjamin Pflugmann wrote:
>> 
>> Hi.
>> 
>> IMHO, it has nothing to do with the index being a binary tree, but
>> that MySQL saves any string (VARCHAR as CHAR) in an index with its
>> whole width. Just think of it as if indexes only know about CHAR.

Jules> Ahh!  Excellent!  Thank you, Benjamin - that does indeed make sense.


>> 
>> This means, although you can get your table smaller by changing CHAR
>> to VARCHAR, you cannot get your indexes smaller.
>> 
>> You might want to use something like
>> 
>> KEY( field1(20) )
>> 
>> which will only use 20 bytes for the field index (and is quite fast,
>> too, as long as the values of field1 are different enough in the
>> leftmost 20 bytes), but this will obviously not work for UNIQUE.

Jules> Hmm.  Well, it is the case that nearly all records are unique in the
Jules> first 20 characters in my database.  I suppose I could live without the
Jules> unique (which is to say, I could implement that in application-level
Jules> logic).

Jules> I'll also check out the source code, and see how hard it would be to
Jules> change this... I can't see any reason to make b-tree nodes have uniform
Jules> length (apart from possibly reducing the storage size of a node_ptr, but
Jules> that would not be a huge gain).

Hi!

The nodes doesn't have the same length;  (ISAM and MyISAM does key
prefix compression and space compression).  I would need some more
information (see previous mail) to help you solve this!

Regards,
Monty