I understand that determined by the database storage engine being used, a performance benefit are available if all the rows within the table could be certain to function as the same length (by staying away from nullable posts and never using any VARCHAR, TEXT or BLOB posts). I am not obvious how far this is applicable to InnoDB, using its funny table plans. Let us give a good example: I've the next 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 real column that's nullable is
Notes, that is MEDIUMTEXT. This table is now offering 33097 rows (that we appreciate is small up to now). Of those rows, only 61 have values in
Notes. The amount of a noticable difference might I see from, say, adding a brand new table to keep the Notes column in and carrying out LEFT JOINs at the appropriate interval?
Text and Blob fields are saved outside of the table (which requres additional on disk research for that value). Every time a subquery is a result of the table will need a brief table, the temporary table is going to be immediatelly be produced on disk, since mysql can't predict the row size and reserve enough space in memory.
If how big the important points area doesn't exceed 64k, I would suggest using VARCHAR rather. When the items in the Notes won't ever exceed 1000 figures, use VARCHAR(1000).