what can I save with int,tinyint,mediumint and so on

Question

I all..

I have always used int(10) for everything, but couple days ago, I started a new project, and was hoping to do this 100% optimized ;)

So I am wondering, how many;

user_id => int(6) vs. mediumint (8) or similar will be possible to create/add

group_id => tinyint(1) vs tinyint (4) or similar will it be possible to create/add

and so on..

I know that the (X) is the width of the field, but, I can not quite understand the actual number of users/posts/messages ++ that can be created using example; mediumint(8) for id, instead of int(10).

Thanks for any reply on this!!

-Tom

[RTFM](http://dev.mysql.com/doc/refman/5.0/en/integer-types.html) — user1191247, Apr 09 '12 at 14:22
See this question and answer: http://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20-etcc — Cfreak, Apr 09 '12 at 14:26
Okei.. So if I understand this correct.. For my example scenario; Using Int, mediumint for user.id, will have nothing to do with how MANY users can be created? Like say; mediumint(8) will be able to save 8million users? Any practical example? — Tom, Apr 09 '12 at 15:01
It has an effect on the number of unique values you can use. For example, if you used a `TINYINT UNSIGNED`, then you can have a maximum of 255 users with unique IDs. That being the case, I think an `INT UNSIGNED` column might be most appropriate (but it really depends on your application), so you can have up to 4,294,967,295 unique IDs. Do make sure to make the column `UNSIGNED`, which means that you cannot have negative values, but it doubles the amount of unique values you can have. — Travesty3, Apr 09 '12 at 16:22
@Travesty3: Both INT and INT UNSIGNED have the same number of distinct values. — Mike Sherrill 'Cat Recall', Apr 10 '12 at 01:23
@Catcall: Have you ever designed a table that has negative values for a user ID? Forgive my wording for not being 100% technically correct...I guess I should have said that it doubles the amount of unique *positive* values you can have. — Travesty3, Apr 10 '12 at 12:19
@Travesty3: Your wording wasn't less than 100% technically correct; your wording was simply wrong. And yes, I've designed tables that use negative id numbers for surrogate keys. Surrogate keys aren't usually exposed to the user; there's no compelling reason to avoid negative numbers. And as far as I know, no other dbms supports unsigned integers as a native data type. (I believe the SQL standards define only signed integers.) — Mike Sherrill 'Cat Recall', Apr 10 '12 at 12:31
@Catcall: Do you really think the OP is planning on using negative values for his user ID field? I don't. So, assuming that he will only be using positive values (which is the **most common** practice), then using `INT UNSIGNED` ***does*** effectively double the amount of unique values you can use. Who cares what other DBMs support? This question is tagged for MySQL. The point is, if you will only use positive values and you use `INT UNSIGNED` instead of `INT`, then you double the amount of values you can use. Is there any need for further argument? — Travesty3, Apr 10 '12 at 13:07
Thanks all for all the info ;) I really appreciate it. Now I am starting to understand this ;) Final question; I had a look at phpBB forum sql, and I noticed they are using mediumint(8) UNSIGNED for everything. Reason I am asking, will not the db-query be "faster" the less byte the tables uses? Eg; How many users possible with mediumint(8)UNSIGNED? Thanks again for your time, you are awesome! — Tom, Apr 10 '12 at 14:31

eabraham · Answer 1 · 2012-04-09T20:30:04.933

5

Database IDs are usually always positive (0->∞) so the max value would be:

Integer Type    Max Value
TINYINT         255
SMALLINT        65535
MEDIUMINT       16777215
INT             4294967295
BIGINT          18446744073709551615

edited Apr 09 '12 at 20:30

answered Apr 09 '12 at 20:13

eabraham

4,094
1
23
29

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-04-11T13:15:58.440

4

I know that the (X) is the width of the field

The optional number in parens is the display width. It has nothing to do with how many unique values are in the range of the integer or how much storage space the integer needs. Application code is free to ignore your hint about the display width. "Display width" is a non-standard extension to SQL.

INTEGER(6) and INTEGER(2) both take 4 bytes to store, and both accept values ranging from -2147483648 to 2147483647.

All medium integers take 3 bytes to store, and accept values ranging from -8388608 to 8388607.

Assuming that a medium int is big enough (~ 16 million unique values) to identify your full domain of values you potentially save 1 byte per row over a 4-byte integer. (Potentially, because some platforms require padding to the next word boundary. For 32-bit systems, that would be 4 bytes, so no actual space savings. I don't know whether MySQL does that.) For 10 million rows, you might save 10 megabytes (plus some space savings in the index)--not very much these days. Narrower tables are generally faster than wider tables, but I don't think you'll notice the difference here.

edited Apr 11 '12 at 13:15

answered Apr 11 '12 at 13:05

Mike Sherrill 'Cat Recall'

91,602
17
122
185

Thanks for an forfilling answer ;) One follow-up. HOW many unique user ids possible if I use MEDIUMINT(8) UNSIGNED for User.ID in mysql 5? I just need to know if I understand this now ;) oh, and DISPLAY width, I got that before from somewhere else, so only poor selection of words! – Tom Apr 11 '12 at 18:01
1

You get exactly the same number of distinct integrs with MEDIUMINT as you get with UNSIGNED MEDIUMINT: 16,777,215. The only difference is the endpoints. (In the first case, -8,388,608 to 8,388,607. In the second case, 0 to 16,777,215.) So the maximum number of unique id numbers you can get with MEDIUMINT is 16,777,215. (In practice, you'll get somewhat less than that.) – Mike Sherrill 'Cat Recall' Apr 11 '12 at 20:00
Okei.. that was how I understood it.. I think ;) Thanks for your time!!! Awesome! – Tom Apr 12 '12 at 14:49
16,777,216. [Fencepost error](http://catb.org/jargon/html/F/fencepost-error.html). – Mike Sherrill 'Cat Recall' Apr 12 '12 at 18:56

what can I save with int,tinyint,mediumint and so on

2 Answers2