List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:January 15 2003 1:18pm
Subject:Re: Avg_row_length
View as plain text  
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