Numeric types and row size in MySQL

Question

I have a user table structured like this:

• id MEDIUMINT(7),
• username VARCHAR(15)

Would it technically be faster if I changed it to this instead:

• id MEDIUMINT(5),
• username VARCHAR(15)

I'm confused because even though the total row length in terms of characters and digits would be shorter, I assume the number of bytes used would be the same.

Answer 1

I'm confused because even though the total row length in terms of characters and digits would be shorter, I assume the number of bytes used would be the same.

You're correct - the number of digits specified does not change the number of bytes that a MEDIUMINT column will use to hold the value. Effectively, there's no speed performance.

Reference:

• Numeric Types

Answer 2

No, it would be no faster. The number after MEDIUMINT is just the display width. It affects only the way in which query results are displayed in some contexts.

Answer 3

The argument after MEDIUMINT makes no difference to its storage or the range of values it supports. It's only a hint for display width. Mostly this is relevant only when you use the ZEROFILL option.

CREATE TABLE foo (num MEDIUMINT(7) ZEROFILL);
INSERT INTO foo VALUES (1234);
SELECT num FROM foo;

0001234

MEDIUMINT is always three bytes.