InnoDB Disk Overhead

Question

I have a table containing two 32 bit integers. These two integers are used to create a clustered index. The SQL for creating the table is as follows:

CREATE TABLE `a` ( `var1` int(10) unsigned NOT NULL, `var2` int(10) unsigned NOT NULL, PRIMARY KEY (`var2`,`var1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 

• There are 6921338 rows in this table (using SELECT COUNT(*) FROM a )
• The table uses 358580224 bytes of space (obtained using SHOW TABLE STATUS )

According to my calculations, each line uses 51.8 bytes. I know that InnoDB has overhead rather than storing strings . However, using the MySQL website, I calculated that the row size should be 26 bytes (5-byte header, 8 bytes for integers, 6-byte transaction ID and 7 bytes).

I am trying to reduce row size because a table can be filled up to 160 billion records.

What am I missing with my calculations? How to optimize a table to use less space? Or do I need to switch to another database engine?

Update

The queries I create for this table;

 INSERT INTO a(var1,var2) VALUES(INTEGER,INTEGER),(INTEGER,INTEGER),...,(INTEGER,INTEGER); SELECT var1 FROM a WHERE var2=INTEGER; DELETE FROM a WHERE var2=INTEGER; 

Answer 1

This is because the index you created also takes up space. In the case of a clustered index in InnoDB, row data is actually stored inside the nodes of the index sheet, so when the index is used, the index does not point to the row, but instead points to the node index sheet that contains the data row along with PKEY.

This has led to a significant increase in storage.

Look at here