Record Size Calculation

[sa_wahab]

Hi,

1. I want to calculate size of the record by mysql queries. Is it
possible..
2. What is the best way to calculate table record size which consists
of Text type.

Regards,
@wahab.

[Bill Karwin]

<sa******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

1. I want to calculate size of the record by mysql queries. Is it
possible..
See http://dev.mysql.com/doc/refman/5.0/...uirements.html
2. What is the best way to calculate table record size which consists
of Text type.

TEXT types, like other BLOB types and VARCHAR, use a variable amount of
storage per record, based on the content stored there. So it's hard to
predict the amount of storage accurately. You have to fetch each record and
see what's stored there.

Regards,
Bill K.

[Xicheng]

sa******@yahoo.com wrote:

Hi,

1. I want to calculate size of the record by mysql queries. Is it
possible..
2. What is the best way to calculate table record size which consists
of Text type.

Regards,
@wahab.

you may do it outside mysql, for example(under WinXP):

mysql -e "select field1, field2, field3 from mytable" mydb -p > a.txt

and then handle the file which contains tab separated records:

perl -lne " tr/\t//; $t += length }{ print$t" a.txt

this command trims tab and newlines, and then adds up the number of
characters in each line. $t finally prints the total number of
characters you have in your query result.

Best,
Xicheng

[sa_wahab]

Bill Karwin wrote:

<sa******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

1. I want to calculate size of the record by mysql queries. Is it
possible..

See http://dev.mysql.com/doc/refman/5.0/...uirements.html

2. What is the best way to calculate table record size which consists
of Text type.

TEXT types, like other BLOB types and VARCHAR, use a variable amount of
storage per record, based on the content stored there. So it's hard to
predict the amount of storage accurately. You have to fetch each record and
see what's stored there.

Regards,
Bill K.

Thx for ur reply Mr.Bill.

Actually my requirement is to limit the user storage like how email
clients doing. If i do caculation on everytime when the page display,
that would be slower the server performance. Instead, is it nice if
calculate the amount of storage then store in the same record in a
clolumn while insert or update, and while fetching just SUM the storage
column. Or is there any other way!!!

And let say.. if i have value 12 in INT type column, and I have value
12345 in other INT type column.. does both occupy 4 bytes each.

Regards,
@wahab.

[Jeff North]

On 12 Feb 2006 19:02:32 -0800, in mailing.database.mysql
sa******@yahoo.com
<11**********************@z14g2000cwz.googlegroups .com> wrote:

|
| Bill Karwin wrote:
| > <sa******@yahoo.com> wrote in message
| > news:11**********************@o13g2000cwo.googlegr oups.com...
| > > 1. I want to calculate size of the record by mysql queries. Is it
| > > possible..
| >
| > See http://dev.mysql.com/doc/refman/5.0/...uirements.html
| >
| > > 2. What is the best way to calculate table record size which consists
| > > of Text type.
| >
| > TEXT types, like other BLOB types and VARCHAR, use a variable amount of
| > storage per record, based on the content stored there. So it's hard to
| > predict the amount of storage accurately. You have to fetch each record and
| > see what's stored there.
| >
| > Regards,
| > Bill K.
|
| Thx for ur reply Mr.Bill.
|
| Actually my requirement is to limit the user storage like how email
| clients doing. If i do caculation on everytime when the page display,
| that would be slower the server performance. Instead, is it nice if
| calculate the amount of storage then store in the same record in a
| clolumn while insert or update, and while fetching just SUM the storage
| column. Or is there any other way!!!
|
| And let say.. if i have value 12 in INT type column, and I have value
| 12345 in other INT type column.. does both occupy 4 bytes each.

Yes.
http://dev.mysql.com/doc/refman/5.0/...uirements.html
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Bill Karwin]

<sa******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Actually my requirement is to limit the user storage like how email
clients doing.
MySQL has no support for quotas to limit storage per user.
And let say.. if i have value 12 in INT type column, and I have value
12345 in other INT type column.. does both occupy 4 bytes each.

That's correct. The storage required by a given numeric data type is not
affected by the value you store there. You can't get space back by storing
negative values! :-)

Regards,
Bill K.

[Michael Austin]

sa******@yahoo.com wrote:

Hi,

1. I want to calculate size of the record by mysql queries. Is it
possible..
2. What is the best way to calculate table record size which consists
of Text type.

Regards,
@wahab.

look at:

http://dev.mysql.com/doc/refman/5.0/...les-table.html
*note* version 5.0+

select table_schema,table_name,avg_row_length,max_row_len gth from
information_schema.tables;

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)