Data Types

One thing is not clear to me in this explanation
*Data Type* *Storage Required*
|CHAR(/|M|/)| |/|M|/| bytes, 0 |<= /|M|/ <=| 255
|VARCHAR(/|M|/)| /Prior to MySQL 5.0.3/: /|L|/ + 1 bytes, where |/|L|/
<= /|M|/| and 0 |<= /|M|/ <=| 255. /MySQL 5.0.3 and later/: /|L|/ + 1
bytes, where |/|L|/ <= /|M|/| and 0 |<= /|M|/ <=| 256 /or/ /|L|/ + 2
bytes, where |/|L|/ <= /|M|/| and 256 |< /|M|/ <=| 65535 (see note below).
|BINARY(/|M|/)| |/|M|/| bytes, 0 |<= /|M|/ <=| 255
|VARBINARY(/|M|/)| /|L|/+1 bytes, where |/|L|/ <= /|M|/| and 0 |<=
/|M|/ <=| 255
|TINYBLOB|, |TINYTEXT| /|L|/+1 byte, where /|L|/ < 2^8
|BLOB|, |TEXT| /|L|/+2 bytes, where /|L|/ < 2^16
|MEDIUMBLOB|, |MEDIUMTEXT| /|L|/+3 bytes, where /|L|/ < 2^24
|LONGBLOB|, |LONGTEXT| /|L|/+4 bytes, where /|L|/ < 2^32

|VARCHAR| and the |BLOB| and |TEXT| types are variable-length types. For
each, the storage requirements depend on the actual length of column
values (represented by /|L|/ in the preceding table), rather than on the
type's maximum possible size. For example, a |VARCHAR(10)| column can
hold a string with a maximum length of 10. The actual storage required
is the length of the string (/|L|/), plus 1 byte to record the length of
the string. For the string |'abcd'|, /|L|/ is 4 and the storage
requirement is 5 bytes.

(found here:
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements. html )

is L determined by row or by column?

So say I've got a field myField TEXT and in it I have the following values
"fred"
"joe's bar and grill"
"Sometimes people enter really really long text that goes on and on for
ever. maybe they ever write a whole book. You know how some people are,
they're just really verbose so even though most of the data is likely
under 256 characters there's no way to guarantee that and the business
rules don't allow for forcing this data to be short so you need a longer
data type."

is it like this:
row 1: L=5
row 2: L=21
row 3: L=370

or is it
row 1: L=370
row 2: L=370
row 3: L=370

If it's the latter, does anyone have suggestions for storing data like this?

Thanks
Joelle Tegwen




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Data Types

--=_alternative 006FE1C8852570D2_=
Content-Type: text/plain; charset="US-ASCII"

Response intermingled. Please read below....

Joelle Tegwen wrote on 12/09/2005 02:54:06 PM:

> One thing is not clear to me in this explanation
> *Data Type* *Storage Required*
> |CHAR(/|M|/)| |/|M|/| bytes, 0 |<= /|M|/ <=| 255
> |VARCHAR(/|M|/)| /Prior to MySQL 5.0.3/: /|L|/ + 1 bytes, where
|/|L|/
> <= /|M|/| and 0 |<= /|M|/ <=| 255. /MySQL 5.0.3 and later/: /|L|/ + 1
> bytes, where |/|L|/ <= /|M|/| and 0 |<= /|M|/ <=| 256 /or/ /|L|/ + 2
> bytes, where |/|L|/ <= /|M|/| and 256 |< /|M|/ <=| 65535 (see note
below).
> |BINARY(/|M|/)| |/|M|/| bytes, 0 |<= /|M|/ <=| 255
> |VARBINARY(/|M|/)| /|L|/+1 bytes, where |/|L|/ <= /|M|/| and 0 |<=
> /|M|/ <=| 255
> |TINYBLOB|, |TINYTEXT| /|L|/+1 byte, where /|L|/ < 2^8
> |BLOB|, |TEXT| /|L|/+2 bytes, where /|L|/ < 2^16
> |MEDIUMBLOB|, |MEDIUMTEXT| /|L|/+3 bytes, where /|L|/ < 2^24
> |LONGBLOB|, |LONGTEXT| /|L|/+4 bytes, where /|L|/ < 2^32
>
> |VARCHAR| and the |BLOB| and |TEXT| types are variable-length types. For

> each, the storage requirements depend on the actual length of column
> values (represented by /|L|/ in the preceding table), rather than on the

> type's maximum possible size. For example, a |VARCHAR(10)| column can
> hold a string with a maximum length of 10. The actual storage required
> is the length of the string (/|L|/), plus 1 byte to record the length of

> the string. For the string |'abcd'|, /|L|/ is 4 and the storage
> requirement is 5 bytes.
>
> (found here:
> http://dev.mysql.com/doc/refman/5.0/en/storage-requirements. html )
>
> is L determined by row or by column?


Based on your examples (below), by row.


>
> So say I've got a field myField TEXT and in it I have the following
values
> "fred"
> "joe's bar and grill"
> "Sometimes people enter really really long text that goes on and on for
> ever. maybe they ever write a whole book. You know how some people are,
> they're just really verbose so even though most of the data is likely
> under 256 characters there's no way to guarantee that and the business
> rules don't allow for forcing this data to be short so you need a longer

> data type."
>
> is it like this:
> row 1: L=5
> row 2: L=21
> row 3: L=370

Yes, it's like this. Each field is individually sized and only takes up
the appropriate ammount of storage on disk. This is not the case with the
earlier versions of NDB tables. They were always fixed width but that has
recently changed. Besides, I do not think you were asking about NDB's
in-memory allocation scheme, were you?


> or is it
> row 1: L=370
> row 2: L=370
> row 3: L=370

This is an example of neither fixed-width or variable storage as you are
asking the database to resize a storage location based on the largest
length added to the database. It would be quite inefficient to program a
database this way. Imagine if you had 10000 rows of data setup like this
and added a new row where the text field was 400 characters long, you
would have to resize all 10000 previous rows to match the new value. It's
easier to either let each field have its own size (as above) or completely
fixed throughout. The advantage of being completely fixed is in lookup
speed. You always know exactly how far to move in memory/disk to get to a
particular piece of data. The downside to completely fixed field sizes is
that it wastes so much space.

>
> If it's the latter, does anyone have suggestions for storing data like
this?
>
> Thanks
> Joelle Tegwen
>

Good question!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 006FE1C8852570D2_=--

Re: Data Types

>>"fred"
>>"joe's bar and grill"
>>"Sometimes people enter really really long text that goes on and on for
>>ever. maybe they ever write a whole book. You know how some people are,
>>they're just really verbose so even though most of the data is likely
>>under 256 characters there's no way to guarantee that and the business
>>rules don't allow for forcing this data to be short so you need a longer
>>
>>
>
>
>
>>data type."
>>
>>is it like this:
>>row 1: L=5
>>row 2: L=21
>>row 3: L=370
>>
>>
>
>Yes, it's like this. Each field is individually sized and only takes up
>the appropriate ammount of storage on disk. This is not the case with the
>earlier versions of NDB tables. They were always fixed width but that has
>recently changed. Besides, I do not think you were asking about NDB's
>in-memory allocation scheme, were you?
>
>
>
>
NDB? I'm not sure what that means. We're using InnoDB if that matters. (?)

>>or is it
>>row 1: L=370
>>row 2: L=370
>>row 3: L=370
>>
>>
>
>This is an example of neither fixed-width or variable storage as you are
>asking the database to resize a storage location based on the largest
>length added to the database. It would be quite inefficient to program a
>database this way. Imagine if you had 10000 rows of data setup like this
>and added a new row where the text field was 400 characters long, you
>would have to resize all 10000 previous rows to match the new value. It's
>easier to either let each field have its own size (as above) or completely
>fixed throughout. The advantage of being completely fixed is in lookup
>speed. You always know exactly how far to move in memory/disk to get to a
>particular piece of data. The downside to completely fixed field sizes is
>that it wastes so much space.
>
>
>
Oh sure, that makes tons of sense when you mention it that way.

>Good question!
>
>
>
Thanks! And thank you for your quick, helpful reply.
Joelle Tegwen


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org