I have a user table structured like this:
Would it technically be faster if I changed it to this instead:
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.
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:
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.
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.
It's just a display width. If you use MEDIUMINT(5), it does not use small resource than just using MEDIUMINT. Thanks you for your question. I have a same question.
Reference: 12.2.5 Numeric Type Attributes
The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.
