storage optimization for read-only table

mhuiyang

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.
mysqldesc vc2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c | varchar(64) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysqlselect * from vc2;
+------+
| c |
+------+
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
+------+
10 rows in set (0.00 sec)

mysqlquit
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

Nov 6 '06 #1

Subscribe Post Reply

3346

lark

mh******@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.
mysqldesc vc2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c | varchar(64) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysqlselect * from vc2;
+------+
| c |
+------+
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
| '' |
+------+
10 rows in set (0.00 sec)

mysqlquit
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.

Nov 8 '06 #2

Similar topics

USB Mass Storage

by: Graham Stevenson | last post by:

Hi, I have a USB mass storage device being developed by colleagues, and need to interface that to a Windows Forms application (C#). The device is neither purely a storage device nor an HID...

.NET Framework

DB2 shared memory and extended storage

by: Jeremy | last post by:

Hi all, I'm getting very confused about how DB2 uses shared memory and I wonder if someone could clarify matters for me, please ? We are running 32bit DB2 V7.2 FP9 under AIX 4.3.3 on a machine...

DB2 Database

Disadvantages of register storage class specifier

by: aruna | last post by:

What is the disadvantage of using register storage class specifier?

C / C++

storage allocation?

by: Timothee Groleau | last post by:

Hi, Is it defined in C++ that 2 arrays of the same type defined one after the other are contiguous in memory? like saying: int a1; int a2;

C / C++

Data Storage, Interface and Design

by: Thomas Fjellstrom | last post by:

(if this is the wrong place to ask this, please let me know where the right place is) I've been attempting to come up with a sane, and relatively simple design for a set of objects that separate...

C / C++

Storage of char in 64 bit machine

by: aruna.mysore | last post by:

Hi all, I have a simple definitioin in a C file something like this. main() { char a; ....... int k; }

C / C++

How to get the Optimization level?

by: specialdosa | last post by:

We use DB2-LUW-8.2. I am doing some research on performance issues and I read about the "OPTIMIZATION LEVELS' and their impacts on Joins. How do I get the Optimization level implemented here?...

DB2 Database

How to read .msg file using stuctured storage

by: =?Utf-8?B?c3Vpcw==?= | last post by:

Hi every body, i have a problem with how to read .MSG file (Outlook) using vb.net ? using this articles the author has already explain how to read .MSG file using stuctured storage (vc++) ...

Visual Basic .NET

Good storage solution

by: qwertycat | last post by:

I'm doing a PHP script on FreeBSD which servers multi-TBytes worth of content mostly files ranging in size from 100KB to 600MB. Similar to a file-sharing sites where users upload their...

PHP

c++ optimization

by: kasthurirangan.balaji | last post by:

Hi, Below is the code. #include <fstream> #include <set> #include <string> #include <iterator> #include <algorithm>

C / C++

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA