I am doing a database systems project and have been asked the question 'Is a table with more columns and rows necessarily bigger than a table with less columns and less rows?'.

I know the answer to this is no because I have two tables in front of me, one with far more columns and rows but both tables are 0.2 bytes. Can anyone tell me why this is?

  • 1
    "both tables are 0.2 bytes"? 0.2 bytes is definitely not the size. I am not familiar with mysql, so I do not know if it supports something similar to "sparse columns". If it does, then more rows/columns does not necessary involves more space, if you have many NULL values. – Alexei Feb 9 '18 at 15:16
  • 1
    Regardless of the product, I could have a giant string fills up a nvarchar(max) column and only have 3 records in a 2 column table, where as I could put 15 columns of char(1) and have 10 records. Data size could be larger in the first table. – Random_User Feb 9 '18 at 15:23
  • How do you measure the table sizes? – mustaccio Feb 9 '18 at 16:38

This depends on a lot of factors.

  • InnoDB or MyISAM?
    Which engine you are using for each table matters a great deal, as they all have different on-disk storage methodologies.

  • Fixed-length fields and rows or variable-length fields and rows?
    Fixed length string fields take up space even if empty. Variable-length do not. I'd have to check, but some numeric formats may also vary in the amount of space they take depending on the value or if null is allowed.

  • Pre-allocated disk space or grow on demand?
    Depending on the engine and configuration, you may not even allocate space on the disk based on the amount of data at all. You might allocate the maximum size all at once, or allocate space in set increments.

  • Indexes?
    Indexes take space too!

In short, read up on database engines and how they allocate space on disk: https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html https://dev.mysql.com/doc/refman/5.7/en/data-size.html

Or this question, which touches on the same topics: https://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.