Mysql medium int vs. int performance

Question

I have a simple user table, I think that the maximum users that I am going to have are 300,000.

I am currently using:

CREATE TABLE users ( id INT UNSIGNED AUTOINCREMENT PRIMARY KEY, .... 

Of course, I have many other tables for which users (id) are FOREIGN KEY.

I read that since id will not use the full maximum of INT, it is better to use: MEDIUMINT and this will give better performance.

It's true?

(I am using mysql on Windows Server 2008)

Answer 1

I would sometimes use MEDIUMINT .. on 300K lines .. MEDIUMINT gives u enough space for up to 16M lines (unsigned).

It's not just about the “smaller table size” when you use indexes. The difference can be huge on 27M row tables. Changing 2 columns from INT to MEDIUMINT saved me 1 GB (indexes + table data), so it went from 2.5 GB to 1.5 GB.

Answer 2

This is called micro-optimization, not a problem.
Try to ask (first of all) performance questions based only on real experience, not imagination. And profiling always helps distinguish one from the other.

As for the "300k max" - in real life, the numbers tend to unexpected growth. Why dig a trap for yourself?

Answer 3

There should not be a difference in performance, the only advantage you get is a slightly smaller table size. In any case, for just 300,000 rows, you don’t have to care.