MySQL Forums :: InnoDB :: Row size computation


Advanced Search

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 2194 Michal Kurgan 03/28/2012 11:16AM
Re: Row size computation 1040 Rick James 03/29/2012 09:44PM
Re: Row size computation 1241 Michal Kurgan 04/04/2012 11:00AM
Re: Row size computation 876 Rick James 04/05/2012 09:06AM
Re: Row size computation 1050 Michal Kurgan 04/05/2012 11:02AM
Re: Row size computation 995 Rick James 04/06/2012 08:50PM
Re: Row size computation 1408 Michal Kurgan 04/10/2012 01:15PM
Re: Row size computation 1123 Rick James 04/12/2012 12:11AM
Re: Row size computation 1089 Michal Kurgan 04/12/2012 10:13AM
Re: Row size computation 1082 Rick James 04/13/2012 04:08PM
Re: Row size computation 1270 Michal Kurgan 04/18/2012 02:25PM
Re: Row size computation, block splits, etc 1304 Rick James 04/19/2012 09:54AM


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.