Members Members   Search Search   Help Help   Register Register   Login Login   Home Home


Home » Performance » MySQL » ROW SIZE????
ROW SIZE???? [message #3396] Sun, 10 August 2008 05:32 Go to next message
vetrib2w is currently offline vetrib2w
Messages: 3
Registered: August 2008
Location: CHENNAI
Junior Member
Hi friends,
I need to find the record size when i m fetching the data from mysql table .
example:
id|name | details|
1 |vetri | b2w |
2| peter | dog |
query:
SELECT * FROM example where id='1';
-------
When i execute this query i need to find the size(in bytes) of the resulting output. Exclamation Exclamation Exclamation Exclamation Exclamation Exclamation

Thanks in advance. Sad Sad Sad
Report message to a moderator
Re: ROW SIZE???? [message #3399 is a reply to message #3396 ] Sun, 10 August 2008 09:29 Go to previous messageGo to next message
Speeple is currently offline Speeple
Messages: 91
Registered: August 2006
Member
I'm not quite sure what you're asking?

Do you want to amount of RAM used to allocate the result set?

Do you want the specific data size of the columns (not including other overheads)?

For the latter:

SELECT LENGTH(name) + LENGTH(details) + 4 AS size FROM example WHERE id=1;

LENGTH() returns the byte length of the columns, the +4 is for the numberical INT type.

To calculate for more than one row:

SELECT SUM(LENGTH(name) + LENGTH(details) + 4) FROM example WHERE id=1;

Martin Gallagher | Speeple: The latest news
Report message to a moderator
Re: ROW SIZE???? [message #3400 is a reply to message #3396 ] Sun, 10 August 2008 09:46 Go to previous messageGo to next message
vetrib2w is currently offline vetrib2w
Messages: 3
Registered: August 2008
Location: CHENNAI
Junior Member
I'm not quite sure what you're asking?

Do you want to amount of RAM used to allocate the result set?

Do you want the specific data size of the columns (not including other overheads)?

For the latter:

SELECT LENGTH(name) + LENGTH(details) + 4 AS size FROM example WHERE id=1;

LENGTH() returns the byte length of the columns, the +4 is for the numberical INT type.

To calculate for more than one row:

SELECT SUM(LENGTH(name) + LENGTH(details) + 4) FROM example WHERE id=1;

Thanks for your reply. Very Happy
Is that +4 applicable for all cases.And
i need to get the exact size of the row.
so you people need to guide me whether i need to include overheads r not...
And i can see the size of entire table in myadmin
Like this
Space usage:
Type Usage
Data 2,412 B
Index 2,048 B
Total 4,460 B
but i need the size for row? help me...

Thanks in advance....
Report message to a moderator
Re: ROW SIZE???? [message #3402 is a reply to message #3396 ] Sun, 10 August 2008 09:57 Go to previous message
Speeple is currently offline Speeple
Messages: 91
Registered: August 2006
Member
If you're treating numberical data types as natives then INT is 4 bytes.

If you need to treat as string then again use LENGTH(id)

Native sizes per column:

INT: 4 bytes
MEDIUMINT: 3 bytes
SMALLINT: 2 bytes
TINYINT: 1 bytes

I don't know what application you are developing so I don't know if you need to include overheads.

You can show table information to display average row length.

Martin Gallagher | Speeple: The latest news
Report message to a moderator
Previous Topic:RAND() Efficiency on well indexed columns
Next Topic:complex website database
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]


Current Time: Fri Dec 5 11:50:25 EST 2008

Total time taken to generate the page: 0.00867 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge