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
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