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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Olaf DoschkeSoftware 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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
theGhost_k8Database 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.