ruby on rails - Optimizing MySQL Table Size With The Limit Option -

if understand how mysql tables work, each instance of table consumes full memory allotment, if columns blank. instance, blank text column consume 65536 bytes if null. if few characters later entered column, still consume full 65536 bytes.

in rails migration, can set character limit on each column. instance, might limit text column 500 characters. when doing this, reduce allotted memory column, instead of 65536 bytes, consumes many bytes needed represent 500 characters?

if i'm confident column limited 500 characters, common practice limit within database or within model validations?

for instance, blank text column consume 65536 bytes if null. if few characters later entered column, still consume full 65536 bytes.

incorrect. see data type storage requirements:

storage requirements string types

in following table, m represents declared column length in characters nonbinary string types , bytes binary string types. l represents actual length in bytes of given string value.

 +-----------------------------+-------------------------------------------------+ |          data type          |                 storage required                | +-----------------------------+-------------------------------------------------+ | char(m)                     | m × w bytes, 0 <= m <= 255, w      | |                             | number of bytes required maximum-length | |                             | character in character set                  | +-----------------------------+-------------------------------------------------+ | binary(m)                   | m bytes, 0 <= m <= 255                          | +-----------------------------+-------------------------------------------------+ | varchar(m), varbinary(m)    | l + 1 bytes if column values require 0 – 255    | |                             | bytes, l + 2 bytes if values may require more   | |                             | 255 bytes                                  | +-----------------------------+-------------------------------------------------+ | tinyblob, tinytext          | l + 1 bytes, l < 28                        | +-----------------------------+-------------------------------------------------+ | blob, text                  | l + 2 bytes, l < 216                       | +-----------------------------+-------------------------------------------------+ | mediumblob, mediumtext      | l + 3 bytes, l < 224                       | +-----------------------------+-------------------------------------------------+ | longblob, longtext          | l + 4 bytes, l < 232                       | +-----------------------------+-------------------------------------------------+ | enum('value1','value2',...) | 1 or 2 bytes, depending on number of        | |                             | enumeration values (65,535 values maximum)      | +-----------------------------+-------------------------------------------------+ | set('value1','value2',...)  | 1, 2, 3, 4, or 8 bytes, depending on number | |                             | of set members (64 members maximum)             | +-----------------------------+-------------------------------------------------+