CHAR(5) vs. INT

Question

INFORMIX-SE 7.1:

Assume that col5 CHAR(5) will only contain numbers from 00001 to 99999:

1. a CHAR(5) column uses 5 bytes of storage, how many bytes does an INTEGER use?
2. Does a UNIQUE INDEX on an INT column perform faster than a CHAR(5)?
3. Is SELECT MAX() on an INT column faster than MAX() on the col5 CHAR(5)?

Answer 1

INT uses 4 bytes. And you can store 0 ... 4294967295 (unsigned) with INT

By the way I wouldn't use CHAR, VARCHAR, or any string-y type to store numbers. You are just asking for trouble here. For example, 9 is greater than 10 if you use string comparison. You'll also miss out on a lot of arithmetic operations.

Answer 2

4 bytes for INT. INT is faster!

For more detail, check out the selected answer for this question:

• Database Primary Key C# mapping - String or int

Answer 3

CHAR(5) will always use 5 bytes of space (10 in multi-byte encodings since mySQL 4.1) because every character can store 255 different characters (or even more in multi-byte encodings).

An unsigned SMALLINT (0..65535) will take 2, an unsigned MEDIUMINT (0.... 16,7 Million) 3 bytes.

Bottom line: Using a CHAR type for numeric data is always a waste of space.

As to the other two questions:

Does a UNIQUE INDEX on an INT column perform faster than a CHAR(5)?

Very, very likely yes, because the index doesn't need to be alpha-numeric and can work with a smaller amount of data

Is SELECT MAX() on a INT column faster than MAX() on the col5 CHAR(5)?

Extremely likely yes, because to run a numeric function on a CHAR column, a type cast has to be performed on every record.

Reference:

• Numeric Types in the mySQL manual
• The CHAR and VARCHAR types in the mySQL manual

Answer 4

• INFORMIX-SE 7.1:

Getting closer to a supported version (than 4.10, or 2.10), but still a decade and more out of support.

Assume that col5 CHAR(5) will only contain numbers from 00001 to 99999:

1. a CHAR(5) column uses 5 bytes of storage, how many bytes does an INTEGER use?
2. Does a UNIQUE INDEX on an INT column perform faster than a CHAR(5)?
3. Is SELECT MAX() on an INT column faster than MAX() on the col5 CHAR(5)?

1. 4 bytes for INTEGER in the Informix DBMS.
2. Marginally. Whether it is truly measurable is open to debate, but there is slightly less data to compare (one byte less), so there is less to do.
3. Again, there is unlikely to be a measurable difference. If there's an index on the column, both operations simply access the index. If there is no index, then both operations end up reading the table (a table scan) and there is a minuscule penalty for dealing with 5 characters instead of 4 bytes.