MySQL - Can I query how much disk space certain rows or columns are taking up?

Question

I have a huge table in MySQL and am looking to make it smaller by optimizing the data.

Now I was wondering if MySQL has features that allow me to calculate how many bytes I would save by deleting certain rows or columns?

So something like: select bytes_used(*) from (subquery...), or something like this?

I can of course duplicate the table and compare the storage used after deleting the rows or columns, but that takes up a lot of time. Some data I can migrate or delete and build differently in the app without breaking anything.

This question is about assessing the possible gains and if this course of action is worth pursuing.

Any other help regarding calculation of disk space with MySQL data is also very welcome. I know that you can see how much data a table takes up in phpMyAdmin, but I'm looking further than this.

Addendum: I'm looking for data size on the row or column level, not whole tables.

Joseph

Well worse case dev.mysql.com/doc/refman/8.0/en/storage-requirements.html has all the information you need to compute storage needed and also LENGTH(varchar_field) will return byte length – Dardar

Maybe this will help you: #4524519 – Cu

Thanks for the info, but this is more of a rough estimate and only works for varchar. I was wondering if MySQL has features for exact calculation on row and/or column level. Guessing the answer will be "No"... – Joseph

Answer 1

Getting data size based on rows or columns is not possible, but you can get the data for entire tables like this:

You can query information_schema.TABLES table to get the disk space used by table, e.g.:

SELECT * 
FROM information_schema.TABLES
WHERE TABLE_NAME = `<your_table>`;

This has the following columns (as per the documentation here):

DATA_LENGTH : For MyISAM, DATA_LENGTH is the length of the data file, in bytes. For InnoDB, DATA_LENGTH is the approximate amount of memory allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.

AVG_ROW_LENGTH: The average row length.

These will give you an idea of how much space is used by the table and how much space you will approximately gain if you delete some rows.

Midge

I am actually looking for more detail than this, as this information is for the table as a whole. I'm looking for data size on row or column level, since calculating bytes used is quite complex and is different for each field type. – Joseph

@Joseph there isn't a straightforward way I am afraid. You might need to calculate it on your own based on the type of the column and amount of data stored in each column. The above can give you the average which should be good enough estimate. – Midge

I have suggested an edit to your answer and accepted this. What I want is not possible, but you answer is as close as we can get. – Joseph