469,904 Members | 2,094 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

Record Size Calculation

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.

Feb 11 '06 #1
6 13832
<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.
Feb 12 '06 #2
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

Feb 13 '06 #3

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.

Feb 13 '06 #4
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
---------------------------------------------------------------
Feb 13 '06 #5
<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.
Feb 13 '06 #6
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
:)
Feb 22 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Ace Alexander | last post: by
2 posts views Thread by grayFalcon | last post: by
1 post views Thread by DB_2 | last post: by
1 post views Thread by cdelaney | last post: by
3 posts views Thread by RamaKrishna Narla | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.