Does using smallint datatype over int in mysql actually save memory?

Question

Does using a smallint datatype in a mysql table over a regular int actually improve memory usage? Wouldn't the hardware just allocate a full 64 bit word size for all data anyway? If it doesn't allocate a full word, then wouldn't we see a performance decrease from having to parse out multiple smallints or tinyints from a 64 bit word allocated in memory?

Basically, is there any design/memory/performance benefit to using the following table over the one after it, assuming we know the range of the values stored in the Status column will never exceed the max/min range of smallint? Any insight would be appreciated:

create table `TestTableWithSmallInt` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Status` smallint(11) DEFAULT 0,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table `TestTableWithInt` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Status` int(11) DEFAULT 0,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

tadman · Accepted Answer · 2013-09-26 21:08:10Z

9

You'll theoretically save two bytes per row, a SMALLINT is a 16-bit signed integer versus the INT which is 32-bit signed. The various types have varying storage requirements.

Normally the savings between INT and SMALLINT produces such a slim performance improvement that you'll have a hard time measuring it, especially if there's a small number of fields you're trimming this way.

For the opposite, you'll only want to use a BIGINT when it's conceivable that you might exhaust the number space of an AUTO_INCREMENT flagged field.

You should probably declare them in their bare types, without a length, to get the best fit. INT is preferable to INT(11) and SMALLINT(11) is misleading as it's impossible to get that much precision from a 16-bit value.

answered Sep 26, 2013 at 21:08

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

encrest Over a year ago

Maybe this is more of a hardware question then? My understanding of 64 bit architectures is that even the smallest possible value will still take up a 64 bit word size. If so, wouldn't a smallint still use the same 64 bit chunk of memory as an int? So effectively, declaring a field as smallint would merely impose a maximum value on the field, without actually saving any memory? Thanks for the advice on declaring bare types by the way :)

tadman Over a year ago

That's not the case. Maybe you're thinking of alignment issues, but 16-bit values are aligned on 4-byte boundaries not 8. I really wouldn't worry about this unless you can measure the memory footprint of MySQL in a meaningful way, and I've never had to concern myself with that level of detail. Computers have gigabytes of memory, and there's optimizations to pack data more tightly, it's rarely a concern.

Joop Eggen · Accepted Answer · 2013-09-26 21:07:35Z

1

Data shoveling of compact memory blocks is faster. Only when a programming language comes into play, conversion takes place.

answered Sep 26, 2013 at 21:07

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Collectives™ on Stack Overflow

Does using smallint datatype over int in mysql actually save memory?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related