Mysql medium int vs. int performance

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 UNSIGNED AUTOINCREMENT PRIMARY KEY,
         ....
开发者_JAVA百科

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

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)

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.

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?

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.

If you care about speed micro-optimization then use int.

A disk read access will read a block of bytes, so reading 4 bytes will be the same as reading 3 bytes.

But since 3 bytes integers are not native for CPUs, mediumint needs to be converted before it can be used (memcpy into a 4/8 bytes integer) causing a overhead, while 4 bytes integers are native for CPUs and can be used directly.