InnoDB disk usage overhead

Question

I have a table that holds two 32bit integers. These two integers are used to create the 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 (retrieved by using SELECT COUNT(*) FROM a)
• The table uses 358580224 bytes of space (retrieved by using SHOW TABLE STATUS)

According to my calculations, each row uses 51.8 bytes. I am aware that InnoDB has overheads while storing rows. However, using the MySQL web site, I have calculated that the row size should be 26 bytes (5 byte header, 8 bytes for integers, 6 bytes transaction id and 7 bytes roll pointer fields).

I am trying to reduce the row size because the table may get populated up to 160 billion records.

What am I missing with my calculations? How can I optimize my table to use less space? Or should I switch to a different database engine?

Update

The queries I make 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 takes up space as well. In the case of a clustered index in InnoDB, the row data is actually stored inside the index leaf nodes, so when the index is used the index does not point to the row but instead points to the index leaf node that contains the row data along with the PKEY.

This caused the storage to be much larger.

Take a look here