MySQL Performance: MySQL => Innodb disk space usage

Members

Help

Home

Home » Performance » MySQL » Innodb disk space usage

Show: Today's Messages :: Show Polls :: Message Navigator

Innodb disk space usage [message #3666]

Tue, 28 October 2008 14:07

razdaman
Messages: 26
Registered: May 2007

Junior Member

Hi mysql experts,

I've several times read that innodb maintains an internal primary key of 6 bytes if you do not specify a primary key your self.

I found a table that in theory would benefit from this (I thought). It has no primary key:

CREATE TABLE IF NOT EXISTS `matches_archive` (
`type` enum('LEAGUE','CUP','FRIENDLY') NOT NULL default 'LEAGUE',
`country_id` tinyint(3) unsigned NOT NULL default '0',
`division` tinyint(2) unsigned NOT NULL default '0',
`group` tinyint(3) unsigned NOT NULL default '0',
`cup_id` smallint(3) unsigned NOT NULL default '0',
`datetime` datetime NOT NULL default '0000-00-00 00:00:00',
`club_id_home` mediumint(6) unsigned NOT NULL default '0',
`club_id_away` mediumint(6) unsigned NOT NULL default '0',
`goals_home` tinyint(2) unsigned NOT NULL default '0',
`goals_away` tinyint(2) unsigned NOT NULL default '0',
KEY `club_id_home` (`club_id_home`,`club_id_away`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then recreated the table with primary key: `id` int(10) unsigned not null default '0'

However, it seems that the space usage is exactly the same as before? I've tried to restart the mysql server to check that something wasn't cached. I also tried to change the primary key column to mediumint - the disk usage was still the same!

The table I tested this with had around 150.000 rows. The table on our production server holds around 30.000.000 rows. I used an old mysql5.0.32 on debian.

Could anyone please explain why the space usage remains the same? Thanks in advance.

[Updated on: Wed, 29 October 2008 12:15]

Report message to a moderator