1

Is there any techniques to calculate actual used data size per every SQL table row? Including enabled Indexes and Log records? Sum of field sizes would not be correct because some fields can be empty or data is less than field size. Target is to know, how much exactly data is used per user. Probably I can do this in handler side.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Dzintars
  • 1,410
  • 21
  • 29
  • Possible duplicate of [How to get size of mysql database?](https://stackoverflow.com/q/1733507/608639), [How to get the sizes of the tables of a MySQL database?](https://stackoverflow.com/q/9620198/608639), etc. – jww Mar 12 '19 at 11:42

1 Answers1

1

With the word "exactly", I have to say "no".

Change that to "approximately", and I say

SHOW TABLE STATUS

and look at Avg_row_length. This info is also available in information_schema.TABLES.

But, that is just an average. And not a very accurate average at that.

Do you care about a hundred bytes here or there? Do users own rows in a single table? What the heck is going on?

There are some crude formulas for computing the size of Data rows and Index rows, but nothing on Log records. One of the problems is that if there is a "block split" in a BTree because someone else inserted a row, do you divvy up the new block evenly across all users? Or what?

Rick James
  • 135,179
  • 13
  • 127
  • 222