0

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:

enter image description here

migrated from stackoverflow.com Jul 18 '18 at 4:16

This question came from our site for professional and enthusiast programmers.

  • Duplicate: stackoverflow.com/questions/2991405/… – Alberto Vidales Jul 18 '18 at 3:31
  • It's not. I know the difference between data types. But need to know if there is a performance benefit to use tinyint as Primary Key – rostamiani Jul 18 '18 at 4:07
  • 1
    Alberto - Not a dup of that one -- that talks about space taken; this asks about performance. Until you show the correlation between the two, they are different issues. – Rick James Jul 18 '18 at 5:55
4

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.)

  • 1
    Not in author's case I think. Look at screenshot. Right table (if remove company_id TINYINT and use id INT for reference) will decrease in size. Left table has at least 400 bytes per record, and saving 2 bytes (0.5%) will not lead to a decrease of memory requirements at all - in both cases it is 10 records per storage page. – Akina Jul 18 '18 at 5:32
  • Furthermore, there is a max of 127 companies due to the TINYINT SIGNED, so the table is 'tiny'. – Rick James Jul 18 '18 at 5:53
  • 1- How can I remove company_id and keep the relation? 2- company table is tiny but company_id in project table may be huge and takes 2 more bytes for each record. Is this effective on performance? – rostamiani Jul 18 '18 at 6:01
  • @rostamiani - The performance difference between a 1-byte TINYINT and a 4-byte INT is not worth worrying about. Akina's comment about the percentage difference is an attempt to put things in perspective. – Rick James Jul 18 '18 at 6:25
  • @Akina I updated my answer in response to your comment. – Willem Renzema Jul 18 '18 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.