Each Row size of a table in mysql?

How to get each row size of a table in MySQL i.e total size of the space taken by a row.

Assume a table has BLOB column. Not all row will be of same size. I want to list out all BLOB column i.e text column which is of greater size. Similarly collective size of a single row.

Is it possible to retrieve each row size of a table in a single query?
Software ProgrammerAsked:
Who is Participating?
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
Can you optimize the table design for the requirement  -- Adding a size column? While uploading also include the table size and you don't have fetch the blobs. Fetching blobs like this is not a good idea.
0
 
Dave BaldwinFixer of ProblemsCommented:
It's a little more complicated than that.  More details here: https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
The internal representation of a table has a maximum row size of 65,535 bytes, even if the storage engine is capable of supporting larger rows. This figure excludes BLOB or TEXT columns, which contribute only 9 to 12 bytes toward this size. For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer.
0
 
Software ProgrammerAuthor Commented:
Else it is possible to find the biggest column which takes huge size and sort the table by size of it
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
In the simplest case, when you already assume it's the blobs contributing most of the data, do this for the top 10 largest blob contents:
Select octet_length(ablobcolumn), id from `yourtable` order by 1 desc limit 10

Open in new window


Bye, Olaf.
0
 
theGhost_k8Database ConsultantCommented:
I'm just curious to know what may cause such a requirement? Can something else be done?
Though this may not be a helpful comment, but curious.

"Query avoided is query optimized"
0
 
Software ProgrammerAuthor Commented:
Basically saving images as base64 url as blob clolumn in one case and saving the entire uploaded file in blob column in another case. Now the user wants to retrieve the top 10 files or the top 10 base64 url which takes huge space in the entity or table?
0
 
Software ProgrammerAuthor Commented:
So instead of sorting the blob we can sort the size column. Is that right? if yes, that sounds a valid idea. Please help me with your comments if any so that we can mark this as resolved.
1
 
Software ProgrammerAuthor Commented:
Olaf...What KV said seems to be a valid suggestion and what you think about it? If that is valid, adding a column is not a big issue and we can easily resolve this problem. What you think?
0
 
theGhost_k8Database ConsultantCommented:
Blobs are the things which you should touch only when you need them, very costly operation. If you may store blob file's sizes in a column, index it, all you need is select id from table order by blob_size desc limit 10 and then you may fetch those 10 images from id.
0
 
Software ProgrammerAuthor Commented:
Thanks KV for the suggested solution and thanks Olaf for the alternative.
1
 
Olaf DoschkeSoftware DeveloperCommented:
The way blobs are stored separately from the normal data makes it unideal to fetch them, yes, but it depends, octet_length() might actually get the information about the blob size from the bytes pointing to the blob. This isn't necessarily just a pointer. So in the end this size column already can be part of the blob data.

For example in InnoDB, this is illustrated quite nicely here: https://mysqlserverteam.com/externally-stored-fields-in-innodb/ in the paragraph BLOB Reference.

This reference data contains the blob size and thus the external storage of the blob isn't accessed to determine the length of the blob.

Depending on the storage engine you use for your tables this varies. But in case of InnoDB you may not need an extra size column you fill manually.

Sorry for coming back late and thanks for considering my answer as an assisted solution.

Bye, Olaf.
1
 
Software ProgrammerAuthor Commented:
Olaf that's informative and thanks for your very nice explanation about it....I didn't new that..I thought when we execute the length of the row it would be expensive...Now it is clear that it won't be in the real case...Thanks for the details
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.