List:General Discussion« Previous MessageNext Message »
From:Stefan Hinz Date:January 15 2003 4:18pm
Subject:Re: Avg_row_length
View as plain text  
Steve,

> mysql> create table t2 (c1 varchar(255));
> Query OK, 0 rows affected (0.00 sec)
> mysql> insert into t2 values ('s');
> Query OK, 1 row affected (0.00 sec)

> And this gave table status of
>             Name: t2
>             Type: MyISAM
>       Row_format: Dynamic
>             Rows: 1
>   Avg_row_length: 20

Well, isn't the row length supposed to be 2 (not 20)? I mean 1 byte for
the character 's', plus 1 byte to store the length.

BTW, the manual doesn't say anything about "internal uses" of additional
bits/bytes, AFAIK. Thanks anyway.

Regards,
--
  Stefan Hinz <hinz@stripped>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

----- Original Message -----
From: "Steve Edberg" <sbedberg@stripped>
To: "Stefan Hinz" <hinz@stripped>
Cc: <mysql@stripped>
Sent: Wednesday, January 15, 2003 2:18 PM
Subject: Re: Avg_row_length


> Yep, you're right!
>
> Perhaps an extra byte is automatically reserved, not only for a
> null/not null status bit, but also for other internal use - eg; a
> 'row changed' bit, etc.
>
> I did a quick test:
>
> mysql> create table t2 (c1 varchar(255));
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into t2 values ('s');
> Query OK, 1 row affected (0.00 sec)
>
> And this gave table status of
>
>             Name: t2
>             Type: MyISAM
>       Row_format: Dynamic
>             Rows: 1
>   Avg_row_length: 20
>      Data_length: 20
> Max_data_length: 4294967295
>     Index_length: 1024
>        Data_free: 0
>   Auto_increment: NULL
>
> so obviously the avg_row_length includes extra bytes for mysql
internal info.
>
> Learn something every day!
>
> -steve
>
>
> At 1:03 PM +0100 1/15/03, you wrote:
> >Steve,
> >
> >>  I believe there is an extra byte for the 'null' flag; if you
declare
> >>  this column to be NOT NULL, I'll bet the avg length will be 10.
> >
> >nice bet, but you lose <g>:
> >
> >mysql> DESCRIBE mynotnullisam;
> >+-------+----------+------+-----+---------+-------+
> >| Field | Type     | Null | Key | Default | Extra |
> >+-------+----------+------+-----+---------+-------+
> >| col   | char(10) |      |     |         |       |
> >+-------+----------+------+-----+---------+-------+
> >1 row in set (0.16 sec)
> >
> >mysql> SHOW TABLE STATUS LIKE 'mynotnullisam';
>
>+---------------+--------+------------+------+----------------+--------
-
> >----+
> >| Name          | Type   | Row_format | Rows | Avg_row_length |
> >Data_length |
>
>+---------------+--------+------------+------+----------------+--------
-
> >----+
> >| mynotnullisam | MyISAM | Fixed      |    3 |             11 |
> >33 |
>
>+---------------+--------+------------+------+----------------+--------
-
> >----+
> >
>
>
> --
>
+-----------------------------------------------------------------------
-+
> | Steve Edberg
sbedberg@stripped |
> | University of California, Davis
(530)754-9127 |
> | Programming/Database/SysAdmin
http://pgfsun.ucdavis.edu/ |
>
+-----------------------------------------------------------------------
-+
> | SETI@Home: 1001 Work units on 23 oct 2002
|
> | 3.152 years CPU time, 3.142 years SETI user... and STILL no
aliens...  |
>
+-----------------------------------------------------------------------
-+

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