The data type used to manipulate and store data has a significant impact on memory consumption, the processor power needed to manipulate the database — both at the application server and browser (manipulation using JavaScript) level — and on the storage space needed.
Picking the wrong data type:
- wastes memory (e.g. if you store all small data in a column designed to store large amounts of data)
- causes performance issues (it will be quicker to search for a number than a character string)
Ideally, you should analyze a representative data sample and use this information to decide the data type and sizing.
For a school, the size of the field in which the number of pupils is stored should be based on a statistical study. This allows you to determine if you can use a TINYINT (1 byte, up to 127) or a SMALLINT (2 bytes, up to 32,726). Whatever the situation, the standard choice of an INT (4 bytes, up to 2,147,483,647) is absurd (but unfortunately a common occurrence found during our audits)
Potential saving: up to 8 times less memory and bandwidth consumed. The CPU usage is also cut by the same amount.