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

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

John> I'm not sure this is the correct list for this question,
John> however the behaviour I'm seeing /is/ with the 4.0 tree.

John> By default, the myisam indexes are packed. When an insert
John> is processed, a char/varchar/text unique indexed field
John> is checked with _mi_prefix_search(). Like the other
John> search functions, this returns a match if the new
John> field is the same or a subset of a field in the index.
John> e.g. if 'foobar' is in the index, then 'foo' returns
John> a match.

John> If the indexes are not packed, then _mi_bin_search()
John> is called. It will also return a match if the new value
John> is a subset of a value in the index. Where the unpacked
John> case differs, though, is that the new field value is
John> expanded to the index width before the index lookup,
John> so there is never a case where a shorter value can be
John> matched against a longer one. e.g. If the index is
John> CHAR(10) then 'foobar' is in the index as 'foobar    '
John> and 'foo' is looked up as 'foo       ', so there
John> is no match.

John> This behaviour doesn't seem right to me. Maybe I'm
John> missing something fundamental. I thought the
John> PACK_KEYS table/index option was simply to save
John> space and speed lookup at the expense of a longer
John> insert. I didn't expect the behaviour of the index
John> to change.

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?

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