Make WordPress Core

Opened 12 years ago

Closed 12 years ago

#2796 closed defect (bug) (wontfix)

INT(4) in database

Reported by: link92 Owned by:
Milestone: Priority: normal
Severity: normal Version: 2.1
Component: Optimization Keywords:
Focuses: Cc:


In the posts table:

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

Surely this would be better to have as SMALLINT?

Change History (9)

#1 @westi
12 years ago

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?

#2 @technosailor
12 years ago

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.

#3 @westi
12 years ago

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.

#4 @link92
12 years ago

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

#5 @link92
12 years ago

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

#6 @westi
12 years ago

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.

#7 @doit-cu
12 years ago

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.

#8 @shorty114
12 years ago

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

Someone want to close this as "wontfix"?

#9 @Nazgul
12 years ago

  • Resolution set to wontfix
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.