MySQL-Con Tips of the Day: AI Index Key Size and Ints

Don't use BIGINT as your auto-increment primary key. Wait.  Make that "Never".

Use "int unsigned auto-increment" instead.  Unsigned ints give you 4.3B potential rows.  Not even FaceBook has that many users.  If you're using unsigned auto-increment ints and you're running out of indexes, you've got bigger problems.

Not only that, resizing the index keys down will save your disk storage indexing space by as much as 50%.  Quicker recovery times.

Also, int(1) is still 4-bytes.  I loved that.  If you need single byte use tinyint or, better, enum.  Enum provides single byte values in readable form but automagically maps the k-v pairs for you in the meta table.  4-bytes down to one byte is a pretty huge percentage for compressing data.

But the best quote I heard today dealt with general advice on storing compressed innoDB data - and this was from the FB engineers who said:

"If your data is terse, make it dense."

This conference is awesome...