List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:June 19 2001 12:15pm
Subject:Re: Index behaviour with packed/unpacked keys.
View as plain text  
Hi!

>>>>> "John" == John Birrell <jb@stripped> writes:

John> On Mon, Jun 18, 2001 at 06:32:18PM +0300, Michael Widenius wrote:
>> The above is a design flaw (of which I am aware of) in how
>> we pass keys to the search functions.
>> 
>> During MySQL usage shouldn't be notable, but we need to fix this
>> in 4.0 or 4.1 to be able to implement VARCHAR more efficiently.
>> 
>> The idea would be to instead of filling up keys with space, we would
>> always use the format:  (2 byte length) + string.
>> 
>> (We already use this format with keys on blob)
>> 
>> It's not that hard to change this;  It's just a lot of code to check
>> when doing this.
>> 
>> How did you notice this?

John> As a test for my application, I import data that I have sucked from
John> a system that runs on the software I dislike so much that I want
John> to write my own. 8-) It's a good test for me, because the data is
John> not contrived.

John> I have a 'user' table:

John> usrnum  INTEGER AUTO_INCREMENT
John> name    VARCHAR(32)
John> mailto  TEXT

John> PRIMARY KEY ( usrnum )
John> UNIQUE INDEX usrname ( name )

John> When I import the data, most of the user rows are inserted, but
John> a few are rejected with ER_DUP_ENTRY. I looked at the data for the
John> rows that failed, using 'mysql' (actually 'mysqle' that I have
John> built embedded). The first one that failed was for the user name
John> 'r1man'. It was failing because there was already a user called
John> 'r1manic'.

You should not get a duplicate key for this case!

I tried to repeat this

create table t1 (id int not null auto_increment primary key, username varchar(32) not
null, unique (username));
insert into t1 values (0,"mysql");
insert into t1 values (0,"mysql ab");
insert into t1 values (0,"mysql a");
insert into t1 values (0,"r1manic");
insert into t1 values (0,"r1man");

The above worked without any problems.

What will not work is if you insert:

insert into t1 values (0,"r1man ");

Because MySQL doesn't honor end space in varchar, which in most cases
is not a problem.

Regards,
Monty
Thread
Index behaviour with packed/unpacked keys.John Birrell18 Jun
  • Index behaviour with packed/unpacked keys.Michael Widenius18 Jun
    • Re: Index behaviour with packed/unpacked keys.John Birrell18 Jun
      • Re: Index behaviour with packed/unpacked keys.Michael Widenius19 Jun
        • Re: Index behaviour with packed/unpacked keys.John Birrell19 Jun
          • Re: Index behaviour with packed/unpacked keys.Michael Widenius20 Jun
            • Re: Index behaviour with packed/unpacked keys.John Birrell20 Jun