How much does an InnoDB table benefit from having fixed-length rows?
- by Philip Eve
I know that dependent on the database storage engine in use, a performance benefit can be found if all of the rows in the table can be guaranteed to be the same length (by avoiding nullable columns and not using any VARCHAR, TEXT or BLOB columns). I'm not clear on how far this applies to InnoDB, with its funny table arrangements. Let's give an example: I have the following table
CREATE TABLE `PlayerGameRcd` (
`User` SMALLINT UNSIGNED NOT NULL,
`Game` MEDIUMINT UNSIGNED NOT NULL,
`GameResult` ENUM('Quit',
'Kicked by Vote',
'Kicked by Admin',
'Kicked by System',
'Finished 5th',
'Finished 4th',
'Finished 3rd',
'Finished 2nd',
'Finished 1st',
'Game Aborted',
'Playing',
'Hide'
) NOT NULL DEFAULT 'Playing',
`Inherited` TINYINT NOT NULL,
`GameCounts` TINYINT NOT NULL,
`Colour` TINYINT UNSIGNED NOT NULL,
`Score` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
`NumLongTurns` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`Notes` MEDIUMTEXT,
`CurrentOccupant` TINYINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`Game`, `User`),
UNIQUE KEY `PGR_multi_uk` (`Game`, `CurrentOccupant`, `Colour`),
INDEX `Stats_ind_PGR` (`GameCounts`, `GameResult`, `Score`, `User`),
INDEX `GameList_ind_PGR` (`User`, `CurrentOccupant`, `Game`, `Colour`),
CONSTRAINT `Constr_PlayerGameRcd_User_fk`
FOREIGN KEY `User_fk` (`User`) REFERENCES `User` (`UserID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `Constr_PlayerGameRcd_Game_fk`
FOREIGN KEY `Game_fk` (`Game`) REFERENCES `Game` (`GameID`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci
The only column that is nullable is Notes, which is MEDIUMTEXT. This table presently has 33097 rows (which I appreciate is small as yet). Of these rows, only 61 have values in Notes. How much of an improvement might I see from, say, adding a new table to store the Notes column in and performing LEFT JOINs when necessary?
