CODEWITHSUNDEEP
mysqlperformance
datasn.io
datasn.io

Reputation: 12877

MySQL INT type of 5 bytes?

Is there any way I can have an INT column that uses 5 bytes? The reason for this is INT (4 bytes) that allocates 4 billion integers isn't quite enough for my application yet BIGINT(8 bytes) is an overkill which would hurt performance because MySQL would have to look through much larger disk spaces for anything, especially when we have billions of rows, the extra storage for the primary keys would be many GBs.

Is there something like a 5 bytes INT column in MySQL? Is there something I can easily custom tweak MySQL to get that?

Upvotes: 2

Views: 737

Answers (2)

Rick James
Rick James

Reputation: 142528

You did not say whether it is AUTO_INCREMENT. If not:

mysql> CREATE TABLE DecPk ( d DECIMAL(11,0) NOT NULL PRIMARY KEY ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

would give you up to 99,999,999,999 in 5 bytes.

Alas, an AUTO_INCREMENT cannot be DECIMAL.

The extra 4 bytes in BIGINT (over INT) adds up to approximate N*4MB for a million rows, where N is the number of references to the column. Assuming it is a PRIMARY KEY, here's how to count the references:

  • Data + PRIMARY KEY, together, counts as 1
  • Each secondary key counts as 1 each
  • Other tables storing that id counts as 1

5-byte INT??

  • INT (also INT SIGNED) occupies 4 bytes, with a range of -2 billion to +2 billion.
  • INT UNSIGNED is also 4 bytes, with a range of 0 to +4 billion.
  • BIGINT (signed or unsigned) is 8 bytes.
  • There is no INT between INT and BIGINT.
  • DECIMAL(m,n) takes about m/2 bytes. m is limited to 64 (or maybe 65). Think of it as each 2 digits are stuffed into a byte.
  • DECIMAL(m,0) for m = 10 or 11, will take 5 bytes.
  • DECIMAL(m,2) for m = 7, 8, or 9, will take 5 bytes.
  • Yeah, the size of DECIMAL is complex.

EXPLAIN sometimes shows an INT being used as the key, but says "key_len = 5". This extra 1 is for NULL.

Upvotes: 3

Steven Moseley
Steven Moseley

Reputation: 16355

The simple answer is no - there isn't any 5 byte integer field.

However, when reading deeper into the source of your question, there seems to be some misperception about the overhead associated with a BIGINT.

Yes, you're looking at double the data storage for that column, but every row in MySQL has:

  1. a 13 byte header for your clustered index (PK)
  2. a 6 byte header per indexed record
  3. a 1 byte pointer per non-indexed record

(See here: https://dev.mysql.com/doc/refman/5.1/en/innodb-table-and-index.html#innodb-physical-record)

Thus, you're only really increasing your row size by about 14% by cutting your BIGINT to 5 bytes ((8-5) / (8+13)), assuming it's the only column in your table.

If you use the COMPACT row format and eliminate your PK, you could save 8 bytes per row (reducing your index to 5 bytes).

Your index's performance impact will be negligible with BIGINT vs INT (though eliminating your PK will probably result in some noticeable performance loss, due to loss of clustering).

The storage impact will also be rather negligible - you're looking at ~ 20 GB impact at an ordinality around 8 billion. Modern storage solutions should eat that up.

Upvotes: 4

Related Questions