OK. Let's make this simple. The lack of detail in this answer is intentional!

Consider this:

You insert 1,000,000 rows into a new table, causing it to increase in size so it can hold all the rows. At the end of this load process the table is 1M in size. It's safe to say that it takes 1M to store 1,000,000 rows.

You then decide to delete 999,999 rows, leaving a single row in the table. The table has not shrunk so it is still 1M in size, but obviously has lots of emty space in it. Can we use the same logic as before to make a conclusion? No. If we did we would be saying that it takes 1M to store 1 row. That's obviously nonsense.

So what?

Well, measuring the size of a table by summing all the blocks or bytes gives you the size of the table, not the size of the data it is holding.

Estimating the size of the data based on num_rows and avg_row_length gives you an idea of the size of the data, not the current size of the table holding it.

What next?

Decide what you actually want to know:

- The size of the table currently.
- The size of the data in the table.
- Something else.

Cheers

Tim...