|
Posted by lark on November 8, 2006, 2:55 pm
Please log in for more thread options
mhuiyang@yahoo.com wrote:
> Hello,
>
> According to MySQL document:
> The expected row length for dynamic-sized rows is calculated using
> the following expression:
> 3
> + (number of columns + 7) / 8
> + (number of char columns)
> + (packed size of numeric columns)
> + (length of strings)
> + (number of NULL columns + 7) / 8
>
> However, I found that this formula is not accurate for the following
> trivial case.
> mysql> desc vc2;
> +-------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | c | varchar(64) | YES | | NULL | |
> +-------+-------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
>
> mysql> select * from vc2;
> +------+
> | c |
> +------+
> | '' |
> | '' |
> | '' |
> | '' |
> | '' |
> | '' |
> | '' |
> | '' |
> | '' |
> | '' |
> +------+
> 10 rows in set (0.00 sec)
>
> mysql> quit
> Bye
> $ ls -lt vc2*
> -rw-rw---- 1 mysql users 200 Nov 4 11:02 vc2.MYD
> -rw-rw---- 1 mysql users 1024 Nov 4 11:02 vc2.MYI
> -rw-rw---- 1 mysql users 8554 Nov 4 11:02 vc2.frm
> $ od -x vc2.MYD
> 0000000 0003 0b05 fe01 2702 0027 0000 0000 0000
> 0000020 0000 0000 0003 0b05 fe01 2702 0027 0000
> 0000040 0000 0000 0000 0000 0003 0b05 fe01 2702
> 0000060 0027 0000 0000 0000 0000 0000 0003 0b05
> 0000100 fe01 2702 0027 0000 0000 0000 0000 0000
> 0000120 0003 0b05 fe01 2702 0027 0000 0000 0000
> 0000140 0000 0000 0003 0b05 fe01 2702 0027 0000
> 0000160 0000 0000 0000 0000 0003 0b05 fe01 2702
> 0000200 0027 0000 0000 0000 0000 0000 0003 0b05
> 0000220 fe01 2702 0027 0000 0000 0000 0000 0000
> 0000240 0003 0b05 fe01 2702 0027 0000 0000 0000
> 0000260 0000 0000 0003 0b05 fe01 2702 0027 0000
> 0000300 0000 0000 0000 0000
> 0000310
>
> Note that for 10 empty string values, the disk storage is 200 bytes. If
> we use the fomula, each row should have a length of
> 3
> + (1 + 7) / 8
> + (0)
> + (0)
> + (1)
> + (1 + 7) / 8
> = 6
> So for 10 rows, the expected storage size is 60 bytes.
>
> Where does the extra 140 bytes used for?
>
> I have a case where the table is used only for archiving so it is a
> write-
> once and read-only table. Is there any special syntax for the create
> table
> statement to optimize disk storage without doing compression?
>
> Thanks,
> Minghui
>
i would use an archive engine for this case. mysql will take care of
everything for you.
| 
XML Sitemap