storage optimization for read-only table

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

Re: storage optimization for read-only table

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.