You should define tables such that they occupy minimum space on disk while accommodating all possible values.
If the size is smaller:
- Less data is written to or read from the disk, which makes queries faster.
- The contents on the disk are loaded to the main memory while processing queries. So, smaller tables occupy less space in the main memory.
- Less space is occupied by indexes.
- If you want to store an employee number, for which the maximum possible value is 500,000, the optimum datatype is
MEDIUMINT UNSIGNED
(which occupies 3 bytes). If you are storing it asINT
, which occupies 4 bytes, you are wasting a byte for each row. - If you want to store the first name, for which the length is varying and the maximum possible value is 20, it is optimal to declare it as
varchar(20)
. If you are storing it aschar(20)
, and just a few names are 20 characters long while the remaining are less than 10 characters long, you are wasting space of 10 characters. - While declaring
varchar...