MariaDB table size if FK is empty or Null

Question

Update my question, after negative vote.

I have the following table.

+-------------------------------+
|   tbl_IndexDemo               |
+---------+------+--------------+
|   ID    | INT  | Primary Key  |
|   FK_1  | INT  | Foreign Key  |
|   FK_2  | INT  | Foreign Key  |
|   FK_3  | INT  | Foreign Key  |
|   FK_4  | INT  | Foreign Key  |
+---------+------+--------------+

It has one Primary Key and four Foreign Keys.

I am trying to estimate size (in bytes) of a row.

What is the size if all fields have data? I calculated

 ID     4 bytes + 4 bytes (Index-PK)
 FK_1   4 bytes
 FK_2   4 bytes
 FK_3   4 bytes
 FK_4   4 bytes

 Total per row = 24 bytes

And if FK_3 is emtpy or NULL, what is the size of the row?

I am using MariaDB with InnoDB.

What DBMS version?What "storage engine"? Why that calculation? What did you learn from [the manual](https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html) & google? Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS Please don't insert EDITs etc, make your post the best presentation as of right now. — philipxy, Feb 22 '20 at 21:12
MariabDB uses MySQL SQL but it is a different DBMS. [Read the manuals for the tools you are using.](https://mariadb.com/kb/en/) [Data types](https://mariadb.com/kb/en/data-types/) ["In MariaDB 10.3.7 and later, the InnoDB implementation has diverged substantially from the InnoDB in MySQL."](https://mariadb.com/kb/en/innodb/) [MySQL InnodDB basics.](https://dev.mysql.com/doc/refman/5.6/en/innodb-introduction.html) Googling ... [How to get the sizes of the tables of a MySQL database?](https://stackoverflow.com/q/9620198/3404097) PS "empty FK" doesn't mean anything. Null is not empty. — philipxy, Feb 22 '20 at 22:05

score 2 · Accepted Answer · answered Feb 29 '20 at 07:48

Computing the size of an InnoDB table is far from that simple. But here is a quick and dirty way to estimate. You got 24; now multiply that by 2 or 3. That is, a crude estimate of the row size will be 48-72 bytes.

As for NULL or not, well that will make only a 4 byte difference per INT, if it makes any difference.

Note that there are 4 ROW_FORMAT values possible. This adds hard-to-quantify wrinkles to the calculations. TEXT and PARTITION also make a mess of estimating size.

If you are worried about space, then consider whether you really need INT, which takes 4 bytes and has a limit of 2 billion. Perhaps MEDIUMINT UNSIGNED (3 bytes, max of 16M) would be better -- especially considering that saves 1 byte for each occurrence. You will have at least 3 occurrences -- the column in tbl_IndexDemo, the column in the other table, and the INDEX implicitly created by the FK.

MariaDB table size if FK is empty or Null

1 Answers1