Em geral:
TINY: 1 byte (255)
SMALL: 2 bytes (65.535)
MEDIUM: 3 bytes (16.777.215)
LONG: 4 bytes (4GB)
BIG: 8 bytes (virtualmente infinito)

Textos
TINYTEXT: 1 byte, 255 caracteres;
TEXT: 2 bytes, 65.535;
MEDIUMTEXT: 3 bytes, 16.777.215;
LONGTEXT: 4 bytes, 4GB.

Inteiros
TINYINT: 1 byte
SMALLINT: 2 bytes
MEDIUMINT: 3 bytes
INT: 4 bytes
BIGINT: 8 bytes

Fonte: http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html