4

What does row size mean in mysql? I have a mysql db and table in myisam and it shows

Space usage Type    Usage
Data    1.3     GiB
Index   429.1   MiB
Total   1.7     GiB

Row Statistics Statements   Value
Format  static
Collation   utf8_unicode_ci
Rows    22,000,001
Row length ø    63
Row size ø  83 B
Next autoindex  22,000,002
Creation    Jul 06, 2014 at 10:48 AM
Last update     Jul 06, 2014 at 10:54 AM
Last check  Jul 06, 2014 at 10:54 AM

I don't understand what does the row size mean. I tried searching on Google, but didn't find much explanation about it.

Can I use it to get the final table size after inserting N rows, by doing

row size * N rows = table size ?

Is it correct?

Let's say I am in testing phase, so I dont have much data in my table. Can this formula be used to get the table size after inserting n rows while in production phase?

2

Yes it's the average size of each row in your table.

row size * N rows = table size

Row size = 83 B

Rows = 22,000,001

Row Size times Number of rows:

83 * 22000001 = 1826000083

Convert result in Giga Bytes:

1826000083 / 1024 / 1024 / 1024 = 1.7

Total = 1.7 GiB

Max

1
  • you sir deserve more up votes , thanks for detailed answer. – user16108 Jul 6 '14 at 14:39
1

Table size is made up of 3 main factors

  1. Data

  2. Indexes

  3. Fragmentation

Data size is simply a matter of row size by number of rows.

Index size for MyISAM is dealt with here - (key_length+4)/0.67, summed over all keys).

A great post explaining fragmentation is here and another good one is here. Fragmentation (indexes and actual data) increases the processing overhead (pointer chains to be resolved, I/O thrashing &c.). It's not that the actual amount of data increases, it just looks like that to the OS and the server. See Jaspar's answer here about comparing car maintenance with keeping fragmentation in check.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy