Why is my table size more than 4x larger than expected? (rows*bytes/row)

Question

I'm looking at a simple table in MySQL that has 4 columns with the following sizes,

unsigned bigint (8 bytes)
unsigned bigint (8 bytes)
unsigned smallint (2 bytes)
unsigned tinyint (1 byte)

So I would expect 19 bytes/row.

There are 1,654,150 rows in this table so the size of the data should be 31,428,850 bytes (or about 30 megabytes).

But I can see via phpMyAdmin that the data is taking up 136.3 MiB (not including the size of the Index on bigint 1, smallint, tinyint which is 79 MiB).

Storage Engine is InnoDB and Primary Key is bigint 1, bigint 2 (a user ID and a unique item id).

---

Edit: As requested in the comments, here is the result of a SHOW CREATE TABLE storage

CREATE TABLE `storage` (
 `fbid` bigint(20) unsigned NOT NULL,
 `unique_id` bigint(20) unsigned NOT NULL,
 `collection_id` smallint(5) unsigned NOT NULL,
 `egg_id` tinyint(3) unsigned NOT NULL,
 PRIMARY KEY (`fbid`,`unique_id`),
 KEY `fbid` (`fbid`,`collection_id`,`egg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

If the table is frequently doing insert/delete/update, you may want to try run OPTIMIZE TABLE query to see how much the table can get shrink. there may be defragmentations and unused spaces in the data file.

The data size that phpmyadmin shows you won't be what you expected here. You will see when you create the table first time, it won't show data usage : 0. It will be 16KB or 32KB or something. And the size won't change as you insert records. That's just how innoDB controls the table file as efficient as it thinks.

Check SHOW TABLE STATUS FROM {db_name} and see how much of Avg_row_length each row of the table is. It won't be 19 bytes either

Answer 2

Your indexes have there own tables on disk (though you can't directly 'see' them). The total size of your db is the size of your table and index tables.

Run

show create table <tablename>;

You can see any indexes defined. Imagine adding the total size of your table and a table consisting of the two columns in your primary key. Those added, will get you the size you're seeing.