MySQL: Performance difference between char(4) faster then smallint(5, unisgned)

Unsigned SMALLINT values use two bytes and have values in the range [0, 65535]. CHAR(4) values take four bytes. So, indexing SMALLINT values will make for a smaller index. Smaller is faster. Plus indexes on character columns usually have all sorts of character-set and case-insensitivity monkey business built in to them, which also takes time and space.

But, for a table with at most 65K rows, the effect of this choice will be so small you'll have trouble measuring it. If you build something that's hard to debug, you'll spend your precious time and ten thousand times as much computer time debugging it than it will save.

Design your tables so they match your application. If you're using a four-digit number use SMALLINT.

The next person to work on your code (even if that person is you a year from now) will thank you for a clear implementation.

And keep in mind that MySQL ignores the number in parentheses on INT declarations. SMALLINT(4), SMALLINT(5), and SMALLINT all mean precisely the same thing. MySQL uses the native processor integer datatypes: TINYINT is an 8-bit number, SMALLINT a 16-bit number, INT a 32-bit number, and BIGINT a 64-bit number. Likewise FLOAT is a 32-bit IEEE 754 floating point number and DOUBLE a 64-bit one. The number of digits SMALLINT(4) is a nod to SQL standards compatibility.

As mentioned by O. Jones, SMALLINT will be faster and more space-efficient.

This is related to the following answer: mysql-char-vs-int

Also, MySQL Documentation:
CHAR and VARCHAR types
Integer Types

Case 1: The difference between CHAR(4) and SMALLINT is insignificant. It should not influence you choice of datatypes. Instead, use the datatypes that match the data.

Case 2: If you are comparing TINYINT to VARCHAR(255), the answer is probably different. Note that there is a much bigger difference in the choices.

Case 3: If the choice comes down to whether to "normalize" a column, there are arguments either way. I much prefer using a CHAR(2) for country_code than normalizing in order to shrink to a TINYINT. The overhead of extra normalization always(?) outweighs the space savings.

Another consideration: How many secondary keys are on the table? And how many other tables will you be joining to?

Case 4: PRIMARY KEY(big_string) but no secondary keys. There is no possibly no advantage in switching to an int.

Case 5: Since secondary keys include the PK, consider:

PRIMARY KEY(big_string),
INDEX(foo),
INDEX(bar)

versus

PRIMARY KEY(id),   -- surrogate AUTO_INCREMENT
INDEX(big_string),
INDEX(foo),
INDEX(bar)

The latter will take less disk space.

Another consideration: Fetching a row is far more costly than comparing an int or string. My point is that you should not worry about comparison performance; you should look at the bigger picture when optimizing.

Case 6: USA 5-digit zip code. CHAR(5) (5 bytes) is reasonable. MEDIUMINT(5) UNSIGNED ZEROFILL (3 bytes) is better because it does everything better. (And it is a very rare case of the *INT(n) being meaningful.)

And the debate goes on and on.