Menu
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,
....
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)
961
MEDIUMINT is a medium-sized integer. The minimum and maximum values for MEDIUMINT SIGNED are -8388608 and 8388607 respectively. The range for minimum and maximum UNSIGNED values equals 0 and 16777215 respectively. The size is 3 bytes per row.
TINYINT can be used to store value of 8 bits. The maximum value we can store is 127. We cannot store, for example 987 with 8 bit value. If we try to insert 987 with TINYINT data type, MySQL raises an error.
In MySQL integer int(11) has size is 4 bytes which equals 32 bit. Signed value is : - 2^(32-1) to 0 to 2^(32-1)-1 = -2147483648 to 0 to 2147483647. Unsigned values is : 0 to 2^32-1 = 0 to 4294967295.
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.
177
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?
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
