mysql medium int vs. int performance?

Question

Hi,

I have a simple users table, i guess the maximum users i am going to have is 300,000.

Currently i am using:

 CREATE TABLE users
 (
         id INT UNSIGEND AUTOINCEREMENT PRIMARY KEY,
         ....

Of course i have many other tables that the users(id) is a FOREIGN KEY in them.

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

Is it true?

(I am using mysql on Windows Server 2008)

Thanks.

Answer 1

There shouldn't be a performance difference, the only advantage you get is a slightly smaller table size. Anyways, for just 300'000 rows you shouldn't have to care.

Answer 2

That is called micro-optimization and not an issue.
Try to ask (yourself in the first place) performance questions based only on the real experience, not imagination. And profiling is always for help to distinguish one from another.

As for the "300k max" - in the real life numbers tend to grow unexpectedly. Why to dig a pitfall for yourself?

Answer 3

I would consider using MEDIUMINT sometimes.. on 300K rows.. MEDIUMINT gives u enough room up to 16M rows (unsigned).

It is not only about "smaller table size" when u use indexes.. the difference can be huge on a 27M rows tables.. changing 2 columns from INT to MEDIUMINT saved me 1GB (indexes + table data) so it went from 2.5GB to 1.5 GB.