Re: Huge index file, why?

[Michael Widenius]

Hi!

>>>>> "Benjamin" == Benjamin Pflugmann <philemon@stripped> writes:

Benjamin> Hi.
Benjamin> On Thu, Aug 26, 1999 at 03:47:05PM -0700, scott@stripped wrote:
>> CREATE TABLE mytable {
>> sid varchar(32) binary default '' not null,
>> primary key (sid)
>> };
>> 
>> Since I noticed that the sid was always 22 characters in length, I figured
>> that I could make it a varchar(22), and thus make the index 1/3 smaller.  I
>> did 'ALTER TABLE mytable CHANGE COLUMN sid sid VARCHAR(22) BINARY DEFAULT ''
>> NOT NULL;'.  The before and after ISM and ISD files were _exactly_ the same
>> size.  [I had assumed this would be the case for the ISD.]

Benjamin> Sorry, I don't really know.

Benjamin> Just a note: I once had a problem (with MySQL 3.22.16) when I changed
Benjamin> a CHAR to INT with ALTER TABLE (the original table design was not mine
Benjamin> ;-), that the index apperantly did not work anymore. I dropped the
Benjamin> index and recreated it and it worked fine afterwards. So it *could* be
Benjamin> that ALTER TABLE did not always(?) recreate the keys properly.

Benjamin> I did not bother reporting it, since it was about getting this
Benjamin> database online. Hm. Monty, if you are interested, I can try now
Benjamin> whether I am able to reproduce it.

The problem was probably that you did try to join it from another file 
with a column that was declared differently (in this case MySQL 3.22
didn't always use keys;  MYSQL 3.23 will on the other hand use keys in 
this case)

If the above is not the case, I would like to see an example of this!

Benjamin> Long story short: Just wanted to suggest to drop and recreate the
Benjamin> index and see if something changes. I doubt it, but it may be worth a
Benjamin> try.

Regards,
Monty