Re: Avg_row_length

[Stefan Hinz]

Sergei,

>> any other guru who can explain why 10 = 11?

> 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) |      |     |         |       |
+-------+----------+------+-----+---------+-------+
MySQL>SELECT * FROM myrowisam;
+------+------+------+------+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 |
+------+------+------+------+------+------+------+------+------+
| foo  | foo  | foo  | foo  | foo  | foo  | foo  | foo  | foo  |
+------+------+------+------+------+------+------+------+------+
MySQL>SHOW TABLE STATUS LIKE 'myrowisam';
+-----------+--------+------------+------+----------------+
| Name      | Type   | Row_format | Rows | Avg_row_length |
+-----------+--------+------------+------+----------------+
| myrowisam | MyISAM | Fixed      |    1 |             91 |
+-----------+--------+------------+------+----------------+

Regards,
--
  Stefan Hinz <hinz@stripped>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: [phone]  Fax: [phone]

----- Original Message -----
From: "Sergei Golubchik" <serg@stripped>
To: "Stefan Hinz" <hinz@stripped>
Cc: "Keith C. Ivey" <keith@stripped>; <mysql@stripped>
Sent: Thursday, January 16, 2003 9:16 PM
Subject: Re: Avg_row_length


> Hi!
>
> On Jan 16, Stefan Hinz wrote:
> > Keith,
> >
> > thanks once again! Well, anyone here from the MySQL developer team?
Or
> > any other guru who can explain why 10 = 11?
> >
> > Here's the problem once again:
> >
> > mysql> DESCRIBE myrowisam;
> > +-------+----------+------+-----+---------+-------+
> > | Field | Type     | Null | Key | Default | Extra |
> > +-------+----------+------+-----+---------+-------+
> > | col   | char(10) | YES  | MUL | NULL    |       |
> > +-------+----------+------+-----+---------+-------+
> > mysql> SHOW TABLE STATUS LIKE 'myrowisam';
> > +-----------+--------+------------+------+----------------+
> > | Name      | Type   | Row_format | Rows | Avg_row_length |
> > +-----------+--------+------------+------+----------------+
> > | myrowisam | MyISAM | Fixed      |  109 |             11 |
> > +-----------+--------+------------+------+----------------+
> >
> > Regards,
> > --
> >   Stefan Hinz <hinz@stripped>
> >   Gesch?ftsf?hrer / CEO iConnect GmbH <http://iConnect.de>
> >   Heesestr. 6, 12169 Berlin (Germany)
> >   Tel: [phone]  Fax: [phone]
>
> It's because of NULLs.
> Extra byte is used to mark, if the column is NULL or not.
>
> (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.)
>
> Regards,
> Sergei
>
> --
> MySQL Development Team
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
>  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
> /_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
>        <___/