INT(4) in database

[link92]

In the posts table:

post_category int(4) NOT NULL default '0',

Surely this would be better to have as SMALLINT?

[westi]

Why? - Please explain the benifit.

INT(4) has range of -2147483648 to 2147483647
SMALLINT has range of -32768 to 32767

So we would be limiting blogs to only having 32767 Categories.

Is this what you wanted to achieve?

[technosailor]

32767 is more than any reasonable blog would have, no? Using Int as opposed to SMALLINT means more overhead. I have to check my MySQL notes but I believe that space becomes reserved once part of the table structure.

[westi]

Switching to SMALLINT rather than INT will cause more PHP overhead though as we will have to add checks to ensure we don't try to create category id 32767.

Category ID's being auto incremeting means we don't have to have 32767 categories but just have created and deleted a lot of them.

[link92]

We already should have PHP overhead checking that we don't go over 9999, the limit of INT(4)

[link92]

… and I should add the limit of SMALLINT(4) is identical, just uses less disk space.

[westi]

Unless I read the MYSQL docs wrong INT(4) just means give me an INT but pad the display in query results to 4 characters long if the number is shorter than 4 characters.

[doit-cu]

Westi is correct. INT takes up 4 bytes no matter what number you put in parenthesis after it. SMALLINT takes up 2 bytes. See here: ​http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html.

What's odd to me is that category_id is a bigint, or 8 bytes, or 18446744073709551616 categories. Maybe some standardization should be done across primary/foreign keys here...

I'm personally partial to INT, but that's more because 1) it's familiar (yeah, they all do the same thing), and 2) disks are cheap.

[shorty114]

Personally, I don't see the point either...

Someone want to close this as "wontfix"?

[Nazgul]

As suggested, this would create more overhead instead of increase performance.