Is there a performance benefit to use tinyint instead of int for Primary Key?

Question

Is there a performance benefit to use tinyint instead of int for Primary Key when I know that the number of rows are limited in this table but this key will be used as a foreign key in another huge table? (I will use a lot of joins in queries) I'm using MySQL. For example this is a part of my model:

Answer 1

Yes, there is a performance benefit to using TINYINT vs INT.

Design your tables to minimize their space on the disk. This can result in huge improvements by reducing the amount of data written to and read from disk. Smaller tables normally require less main memory while their contents are being actively processed during query execution. Any space reduction for table data also results in smaller indexes that can be processed faster.

Read the entire page of the documentation where I pulled that paragraph from for even more information.

For your particular case, the use of TINYINT instead of INT in the company table will mean that the foreign key in the project table will 5 bytes, instead of 8. This is because all secondary keys include the primary key of the table itself - this is so that it can look up the primary key record and get more information. (Which leads into a discussion on clustered indexes, covering indexes, and double lookups.)

So if you will be joining on or filtering by this foreign key to the company table, then you will use 5/8ths as much space for each index entry, which means you'll be able to fit about 8/5ths as much more per page lookup, which means less pages have to be loaded into memory to handle any given query.

In general, smaller is always better. Not for the disk space usage per se, but for when you need to have the data loaded into memory for joins and WHERE clause filters.

That said, TINYINT is quite small. Are you sure you will never need more than 256 companies? My personal rule of thumb is take your best guess on how many records you will have, and then pick a datatype 10 times or more larger. Why? Because people are both bad at estimating and at anticipating future use cases. SMALLINT or MEDIUMINT may avoid you pain in switching in the future.

So, go for small, but big enough to make it unlikely to have to change anytime soon (or preferably, ever.)