List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:January 17 2003 4:07pm
Subject:Re: Avg_row_length
View as plain text  
* Stefan Hinz
> * Sergei:
> > It's because of NULLs.
> > Extra byte is used to mark, if the column is NULL or not.
>
> Thanks alot for the explanation. Thanks to Gerald Clark, too, who told
> me the same.
>
> > (in fact it's a bitmap, that is it would be one byte for up to 8
> > columns, then it'll be 2 bytes, etc.)
>
> If so, this table should have an average row length of 92, not 91,
> shouldn't it (9 cols * 10 bytes + 2 bytes)?
>
> MySQL>DESCRIBE myrowisam;
> +-------+----------+------+-----+---------+-------+
> | Field | Type     | Null | Key | Default | Extra |
> +-------+----------+------+-----+---------+-------+
> | col1  | char(10) |      |     |         |       |
> | col2  | char(10) |      |     |         |       |
> | col3  | char(10) |      |     |         |       |
> | col4  | char(10) |      |     |         |       |
> | col5  | char(10) |      |     |         |       |
> | col6  | char(10) |      |     |         |       |
> | col7  | char(10) |      |     |         |       |
> | col8  | char(10) |      |     |         |       |
> | col9  | char(10) |      |     |         |       |
> +-------+----------+------+-----+---------+-------+

These columns are all defined as NOT NULL, the mentioned bitmap does not
apply. The extra byte you see is a deletion flag, used internaly when you
delete a record from a fixed width table. The unused 7 bits of this byte is
used for the bitmap when you use NULLs, allowing up to 15 NULL fields to be
stored with only one additional byte per record: 15 char(10) fields would
give a Avg_row_length of 152, while 16 fields would give a Avg_row_length of
163. If there are fewer than 8 NULL fields, no additional byte is needed for
the bitmap, leaving the Avg_row_length at the combined field length + 1 for
the deletion flag.

--
Roger

Thread
Avg_row_lengthStefan Hinz14 Jan
  • RE: Avg_row_lengthJennifer Goodie15 Jan
  • Re: Avg_row_lengthSteve Edberg15 Jan
  • Re: Avg_row_lengthDan Nelson15 Jan
  • Re: Avg_row_lengthStefan Hinz15 Jan
    • Re: Avg_row_lengthSteve Edberg15 Jan
    • Re: Avg_row_lengthKeith C. Ivey15 Jan
      • Re: Avg_row_lengthStefan Hinz16 Jan
        • Re: Avg_row_lengthKeith C. Ivey16 Jan
          • Re: Avg_row_lengthStefan Hinz16 Jan
            • Re: Avg_row_lengthSergei Golubchik16 Jan
            • Re: Avg_row_lengthZak Greant16 Jan
          • Re: Avg_row_lengthgerald_clark16 Jan
          • Re: Avg_row_lengthStefan Hinz16 Jan
            • Re: Avg_row_lengthSergei Golubchik17 Jan
            • Re: Avg_row_lengthRoger Baklund17 Jan
          • Re: Avg_row_lengthgerald_clark17 Jan
          • Re: Avg_row_lengthStefan Hinz17 Jan
  • Re: Avg_row_lengthStefan Hinz15 Jan
  • Re: Avg_row_lengthStefan Hinz15 Jan