how to get mysql row size in GB

Question

Just ended up with calculating the size of MySQL table in GB with the following query.

SELECT (data_length+index_length)/power(1024,3) tablesize_gb FROM information_schema.tables WHERE table_schema='db' and table_name='tablename'

Is it possible to get the size of a MySQL row in GB.

Or how to get the avg row size for the table in GB.

@nneonneo From above query i'm getting size of table, but i need to get row size. — Parthi04, Mar 30 '13 at 05:56
@nneonneo Yes. I'm considering that too, but will it be a proper way to get ? — Parthi04, Mar 30 '13 at 05:58
I presume that you can use [`AVG_ROW_LENGTH`](http://dev.mysql.com/doc/refman/5.6/en/tables-table.html) too if your MySQL supports it. — nneonneo, Mar 30 '13 at 05:59
@nneonneo in above query, they have calculated data length & index length. when i query AVG_ROW_LENGTH for the table the value i got is not equal to the above query result. it show the data_length alone, its not considering the char_length. — Parthi04, Mar 30 '13 at 06:02
Then you should just divide by the (approximate) row count. No matter how you do this, you will not get an exact result (I think). — nneonneo, Mar 30 '13 at 06:03
AVG_ROW_LENGTH value is equal to (data_length) in above query, so calculating the index_length is must? @nneonneo — Parthi04, Mar 30 '13 at 06:06

score 16 · Answer 1 · edited Dec 29 '20 at 13:12

16

To get the average row length (including overhead), use the AVG_ROW_LENGTH column in the information schema table:

select AVG_ROW_LENGTH from INFORMATION_SCHEMA.tables;

As far as I'm aware, there's no way to calculate the exact actual size of a single, specific row in MySQL.

edited Dec 29 '20 at 13:12

Pikamander2

7,332
3
48
69

answered Mar 30 '13 at 06:01

nneonneo

171,345
36
312
383

Ranil Wijeyratne · Answer 2 · 2016-04-01T10:56:08.583

12

Hi this might do the trick, we had a similar issue and had to find out which types of rows take up the most space. That's why here with a group by...

SELECT groupval, (sum(length(somefield) + length(someotherfield)) / 1024) / 1024 as "fields_size_mb"
FROM table
GROUP BY groupval
ORDER BY fields_size_mb desc;

edited Apr 01 '16 at 10:56

answered Oct 22 '15 at 09:23

Ranil Wijeyratne

626
7
19

score 6 · Answer 3 · answered Jul 13 '17 at 08:29

SELECT 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024 ), 2) as `Size in MB`,
     round((AVG_ROW_LENGTH / 1024), 2) as `Avg row size in KB`
FROM information_schema.TABLES WHERE table_schema = 'your_db_name'
ORDER BY `Size in MB` DESC

score 0 · Answer 4 · answered Jul 31 '18 at 16:46

Not sure if you were looking for that, but I end up here looking for the theorical size of a row (addition of every fields' size).

So I finaly came up with this request :

select TABLE_NAME, sum(CHARACTER_MAXIMUM_LENGTH)/power(1024,3) 
from COLUMNS  where TABLE_SCHEMA = 'schema' group by 1 order by 2 desc ;

score 0 · Answer 5 · answered Apr 05 '19 at 09:16

To calculate the size of a row, use length() method.

For example:

MariaDB [db]> select id,length(row_to_calcsize) from tablename order by id desc limit 2\G
*************************** 1. row ***************************
     id: 501
length(row_to_calcsize): 2192911
*************************** 2. row ***************************
     id: 500
length(row_to_calcsize): 51657
2 rows in set (0.00 sec)

MariaDB [db]>

To calculate size in GB, just divide it 3 times per 1024

length(row_to_calcsize)/1024/1024/1024

score -4 · Answer 6 · edited Apr 02 '13 at 12:25

To find table size we can use something like this..

SELECT count(*) tables,
       concat(round(sum(table_rows)/1000000,2),'M') rows,
       concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
       concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
       concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
       round(sum(index_length)/sum(data_length),2) idxfrac
       FROM information_schema.TABLES
       WHERE  table_name like "%table-name%"

Find the largest table in MYSQL Databases we can use something like this

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10

The question was specifically about finding the largest row size, not the largest table. — coderintherye, Apr 09 '15 at 17:58

how to get mysql row size in GB

6 Answers6

Linked

Related