size calculation of selected rows in MYSQL data base

Question

I want to calculate the exact size occupied by the selected rows in a table. Table has different column data types like int, varchar, date...

example:

SNO(INT) , NAME(VARCHAR) , DATE(DATE)

1 , ABC , 20-01-2017

2 , BCD , 11-01-2017

My aim is to calculate the size occupied totally by the two rows of data with respective to their actual data type storage.

like sum(size(SNO)+size(NAME)+size(DATE))

Can anyone help me to solve this. Thanks

Possible duplicate of [how to get mysql row size in GB](http://stackoverflow.com/questions/15715487/how-to-get-mysql-row-size-in-gb) — Thomas G, Feb 28 '17 at 10:52

score 0 · Answer 1 · answered Feb 28 '17 at 10:58

0

it calculate sum of all column size datatype

SELECT sum(cast(substring_index(COLUMN_TYPE,'(',-1) as signed)) as totalsum,
FROM information_schema.COLUMNS WHERE TABLE_NAME = 'mytable';

here replace with 'mytable' to your table name

answered Feb 28 '17 at 10:58

denny

r u satisfied with this query or not? please leave comment instead of answer – denny Feb 28 '17 at 11:48

score 0 · Answer 2 · edited Feb 28 '17 at 11:46

0

Thanks for your update denny.

that is the sum of column size.May be mistake from my side.I don't want column size.I want data size.

SNO(INT) , NAME(VARCHAR) , DATE(DATE)

1 , ABC , 20-01-2017

2 , BCD , 11-01-2017

like sum( size(1)+size(ABC)+size(20-01-2017)).

size occupied by the data in the memory.

Thanks

edited Feb 28 '17 at 11:46

Shahzad Barkati

answered Feb 28 '17 at 11:17

saran

this is not an answer, this should be a comment on denny's answer or an edit to the question. – Shadow Feb 28 '17 at 11:19

2 Answers2