11

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;
encrest
  • 1,757
  • 1
  • 17
  • 18

2 Answers2

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.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • 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 :) – encrest Sep 27 '13 at 01:09
  • 2
    That's not the case. Maybe you're thinking of alignment issues, but 16-bit values are [aligned on 4-byte boundaries](http://software.intel.com/en-us/articles/data-alignment-when-migrating-to-64-bit-intel-architecture) 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. – tadman Sep 27 '13 at 14:45
1

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

Joop Eggen
  • 107,315
  • 7
  • 83
  • 138