MySQL Forums
Forum List  »  InnoDB

Row size computation
Posted by: Michal Kurgan
Date: March 28, 2012 11:16AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Row size computation
2346
March 28, 2012 11:16AM
1085
March 29, 2012 09:44PM
1286
April 04, 2012 11:00AM
915
April 05, 2012 09:06AM
1082
April 05, 2012 11:02AM
1025
April 06, 2012 08:50PM
1444
April 10, 2012 01:15PM
1156
April 12, 2012 12:11AM
1126
April 12, 2012 10:13AM
1291
April 13, 2012 04:08PM
1315
April 18, 2012 02:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.