MySQL: Two digit 0...53 - use TINYINT(2) or another approach?

Question

I need to store a field in a MySQL TABLE which will only have numbers from 0 to 53. The numbers will never be less than 0 or more than 53. What gives better performance and is the most efficient way to store these?

TINYINT(2)
TINYINT(2) UNSIGNED

Or another method? Thanks!

Interesting, but unless you've got millions and millions of rows, or unless you need to pipe these over the network in huge volumes, then saving a few bytes storage per row won't make a lot of difference. — Rup, Jun 05 '13 at 10:16
From the CPU side, the fastest is to work with normal int (ie : 32bits in 32bits environment and 64bits in 64bits environment) because it fits registers and don't have do to manipulation. From a database view, can't say... — Alexandre Lavoie, Jun 05 '13 at 10:18
Why a numeric type? Are you intending to do calculations on them? — Burhan Khalid, Jun 05 '13 at 10:32

Déjà vu · Answer 1 · 2013-06-05T10:34:16.257

I would go for TINYINT UNSIGNED, since it is a basic type and MySQL knows how to deal with it efficiently. [0-53] is in the 6 bits range: looking at saving 2 bits at the cost of some special structures and the overhead that goes along is probably not a good idea.

And Unsigned since (depending on what language reads data) you want to deal with positive numbers. In the language you use, signed values may have some difference (like right-shifting bits for instance, that will extend the sign bit from left to right, if the value was processed). It depends on your needs and operations.

As for the CPU, the data bus is (usually) 32 or 64 bits. Using 32 instead instead of 8 bits, for instance, may have some advantages performance wise but

obviously, each value takes 4 times more space
performance wise, when retrieving many rows of data, MySQL optimizes the transfer (it doesn't return one byte at a time), so that shouldn't make a big difference.

I forgot to mention this being used with PHP, if that matters. — Edward, Jun 05 '13 at 10:33
@Edward PHP decides on your behalf what structure to associate to a `$` variable. If you want to save some space in case of a huge retrieve, create a string which chars are the bytes you get from the DB. You could add 32 to each byte, to have a string with printable values (if that matters)... and take into account that +32 value later on. — Déjà vu, Jun 05 '13 at 10:45

MySQL: Two digit 0...53 - use TINYINT(2) or another approach?

1 Answers1