MySQL: Optimize your Primary Key

Lately I’ve been reading a lot about MySQL optimization and fine tuning. The other day I read a good tip about the size of your primary key. It will definitely save you space as your tables get bigger.


On every table I’ve created, I’m fairly certain the primary key (PK) has been an int and I never really thought of the size I give it. Usually by default, if you’re using phpMyAdmin or some other DBA app it will likely be 4 bytes. You may think 4 bytes is small and doesn’t really matter but when your tables start getting really big, it can definitely add up.

I was reading some of the links in the PHP Freaks MySQL stickies thread and came across this article which talked about the size of the primary key. See “#3.) (Data) Size Does Matter!” in that article. From their example, if you have 10 tables with 10 millions records and you are using a PK of a BIGINT (8 bytes. No idea why you would use this but it looks like some do just because they can), the primary keys from these tables will take up 760MB. By switching these to a regular int (4 bytes), you can save 380MB, a savings of 50%.

Now when you are creating a table, give it thought on what the maximum number of rows this table might have. Take a look at the number of records each size int can hold.

MySQL Data Types
TypeBytesMinimum ValueMaximum Value
(Signed/Unsigned)(Signed/Unsigned)
TINYINT1-128127
0255
SMALLINT2-3276832767
065535
MEDIUMINT3-83886088388607
016777215
INT4-21474836482147483647
04294967295
BIGINT8-92233720368547758089223372036854775807
018446744073709551615

I don’t think I’ve ever needed negative numbers for my PK so making it unsigned will give you double the number of records. For the most part, I think you can work with unsigned SMALLINT (Max 65K) or MEDIUMINT (Max 16M) for almost all your tables and in the rare cases where you need more than 16 million records, you can use INT. You can always change the size of your PK later on if you find out later that your table is getting full. Another added bonus of minimizing the size of our PK is that now it takes less space to load a record into memory, thus being able to hold more in memory and making the MySQL response time faster.

Enjoy.

Continue Reading

  • This is a great tip. Once these tables start to house hundreds of thousands of rows, every little bite counts.

  • Sean Nolan

    Hmmm… “You can always change the size of your PK later on if you find out later that your table is getting full” – don’t underestimate the impact of changing the size of your PK later on – modifying the type is not trivial especially for large tables. In particular – you will have to modify the type of all the foreign keys that refer to the PK you’re modifying too, so you may be setting yourself up for some very long running ALTER TABLEs. I’m not advocating being wasteful, but I want to be sure that anyone reading this understands that changing the size of a PK is not a simple operation.