Row size computation
I followe docs on storage requirements, but i have no idea why row length is so big. From my computation it should be 49 bytes per row (but i guess each char field will take more space that 3 bytes)
Now table status show 132 bytes(!) per row. What am i missing?
I did alter the table multiple times, i.e. to modify collation. Does it have any inpact on row size
Additionally i see that between runs value of Avg_row_length: and Rows: varies. Any idea what is going on?
$ mysql --version
mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (x86_64) using readline 6.2
mysql> show create table dev_apps\G
*************************** 1. row ***************************
Table: dev_apps
Create Table: CREATE TABLE `dev_apps` (
`dev_id` bigint(19) unsigned NOT NULL,
`app_id` int(10) unsigned NOT NULL,
`app_version` int(10) unsigned NOT NULL,
`launch_count` mediumint(8) unsigned NOT NULL DEFAULT '0',
`crash_count` mediumint(8) unsigned NOT NULL DEFAULT '0',
`ip` int(10) unsigned NOT NULL DEFAULT '0',
`timezone` tinyint(4) DEFAULT NULL,
`lang` char(3) CHARACTER SET utf8 NOT NULL,
`country` char(3) CHARACTER SET utf8 NOT NULL,
`os_id` int(10) unsigned DEFAULT NULL,
`dev_type_id` int(10) unsigned DEFAULT NULL,
`created_at` int(11) DEFAULT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`dev_id`,`app_id`,`app_version`),
KEY `app_INDEX` (`app_id`,`app_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
mysql> show table status like 'dev_apps'\G
*************************** 1. row ***************************
Name: dev_apps
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 321910
Avg_row_length: 132
Data_length: 42582016
Max_data_length: 0
Index_length: 15253504
Data_free: 5319426048
Auto_increment: NULL
Create_time: 2012-03-09 11:55:49
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
mysql> select count(*) from dev_apps;
+----------+
| count(*) |
+----------+
| 313471 |
+----------+