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.